SQL Performance

SQL Performance

• Nick_Zozzo

  hi talha,

   

  how about implementing and indexed view first and then

  have your sp Query the indexed view

   

  regards,

  joey

   

   

  
  
  

• LJMOORE

  hello talha,

  what i was suggesting was like this

  1.  create an "indexed view" on this

  create view XXX

  as

  SELECT ColumnX .....
  FROM    TableX
  INNER JOIN  tabley
  ON TableX.C = TableY.c

  2.  Create the Sp that queries view XXX

  create Sp_nonsargquey on xx

  (

  @surname varchar(200),

  @firstname varchar(200)

  )

  as

  SELECT *  from XXX WHERE ---- here's  where we make use of the view
  (surname = @surname
  OR @Surname is null)

  AND (firstname = @firstname
  OR @firstname is null)

  Indexed view will definetely enhance your query because your eliminating

  the needs to join table from time to time everytime the Sp is run

  besides you can use appropriate index to it. the

  downside of the story is that its going to eat space since it is physical

   I think there can still be improvements that can be made on the where clause which

  as you stated is causing most of the bottleneck.

   

  One suggestion i may suggest is to divide the query into three

  1. one that handles the "not null"   and then saving it to #temp

  2. another query that handles those that are with "null" adding its results set  to the previous

  3. and yet another query that will return the result set from the #temp

   

  another suggestion would be like this

  1. select only the query that qualifies the first where clause

  (surname = @surname
  OR @Surname is null)

  and then store it to #temp1

  2.  From #temp1. apply the second query which qualifies the

  second criteria which is

  (surname = @surname
  OR @Surname is null)

  and so on until the tenth

  or you can group it by 3's or 5's

  first query may satisy the first 3 criteria, then second query satisfy the second batch of criteria and so on. the beauty of this is that you eliminate the impact of the null

  if your using 2k5 you may make use of CTE's

  regards,

  joey

   

   

   

   

   

   

  
  
  

• berk-atabek

  We can not pass parameters to the Views. Thanks for your comments. I think Dynamic SQL is the only way to determine if to use filtering in where clause or not.
  

• Bajju

  There are many techniques that you can use for performing searches with optional parameters. You can find them at the link below:

  http://www.sommarskog.se/dyn-search.html

  One method that I proposed in the past is to do below:

  where surname like coalesce(@surname, '%')

  and firstname like coalesce(@firstname, '%')

  This can be expanded for other data types too like int or datetime by using a low/hi range check.

  
  
  

• Ramesh Kasavaraju

  If it works now I am not sure I would change. If you are using SQL Server 2005, you can use EXECUTE AS to implement security. A really large table with a variable number of search parms is one of the Achilles' heels of stored procedures.

  A question I would have is how many possible combinations of parameters do you have And when you do the different variations of parameters are the universally slow Could you come up with a couple of procedure variations to optimize common cases:

  if @parm1 is not null and @parm2 is null and @parm3 is null ... etc

  exec proc_parm1 @parm1 = @parm1

  else

  exec proc_all @parm1 = @parm1, @parm2 = @parm2, @parm3 = @parm3

  If you don't have too many parameters (or at least too many that cause your procedure to run slower than a frozen snail doing the backstroke in a pool of black strap molasses) this can be a viable solution that will give you the best performance because you optimize each case seperately. It can be way too much work of course, and certainly bad for maintainance, of course...

  
  
  
• SQL Performance