Query Performance

Question

Query Performance

shirish_padekar

SSC Eights!

Points: 830
More actions
July 18, 2015 at 8:13 am

#302595

Hi,
On our webpage we have 8 stored procedures that reads data from Stories table and all these procedures have 4 common conditions in their "where" clause:
Select * from Stories where
(SiteID = @SiteID) and
(Active = 1) and
(StartDate <= @currentDate) and
(EndDate is null or EndDate >= @currentDate)
How can I avoid scanning of Stories table in 8 stored procedures for comparison of 4 where conditions? I was thinking to use table variable to get below result and then pass it to 8 stored procedures for further filtering but max result for below query returns 20,000 records:
Select StoryID from Stories where
(SiteID = @SiteID) and
(Active = 1) and
(StartDate <= @currentDate) and
(EndDate is null or EndDate >= @currentDate)
Is there a better way so as to improve query performance ? At least we would like to avoid/improve date comparison costs.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

First question is why are you trying to reduce the number of procedures? In SQL, generalising code often ends up degrading performance. What are you specifically trying to achieve here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shirish_padekar SSC Eights! Points: 830 More actions · Answer 2

I am not trying to reduce the number of procedures, but trying to avoid repeatative scanning of Story table for common conditions. As I said there are 4 common conditions in where clause of every stored procedure in addition to other filtering criteria. I was thinking to pass result of below query as a TVP to each procedure and then have join with this result for further filtering or process:

Select StoryID from Stories where

(SiteID = @SiteID) and

(Active = 1) and

(StartDate <= @currentDate) and

(EndDate is null or EndDate >= @currentDate)

But looking for better approach.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you're indexing is correct, it shouldn't be scanning.

Copying to a table variable each time each procedure is run and then filtering the table var won't reduce the number of times the base table is read, it'll still be once each time each procedure is called along with the extra cost of inserting into the table variable and reading from it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shirish_padekar SSC Eights! Points: 830 More actions · Answer 4

No, I will scan for common conditions just once:

1. In main procedure I will store below query result in temp table/variable,

You will see that I am just selecting StoryID from below query:

Select StoryID from Stories where

(SiteID = @SiteID) and

(Active = 1) and

(StartDate <= @currentDate) and

(EndDate is null or EndDate >= @currentDate)

2. Now, I will pass above result as TVP to other 8 stored procedures and then will INNER JOIN it with the Stories table and WHERE clause conditions will be different here in all these stored procedures

Using above approach, I did scanning for 4 common conditions in WHERE clause just once in step 1 above and then further filtering will be done by step 2.

Now, max records returned by step 1 are 20,000. So I was thinking is there a better way for above implementation?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ah, so all 8 get called each time, in sequence, every time? That wasn't clear.

I suspect you may be falling into this trap a little: http://www.sqlservercentral.com/articles/Performance+Tuning/115825/

Are these queries of Stories a problem? Can they not be tuned? How much time would you save by eliminating 7 of the queries? Is it worth the extra complexity, the overhead of the temp table (and you do not want to use table variables for this, they have enough other problems), and the work required to implement this?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shirish_padekar SSC Eights! Points: 830 More actions · Answer 6

Stories table is having 1,00,000 records which is used by 50+ sites and these records are increasing everyday.

What if I add a new column "IsArchived" in Stories table and run below query daily using scheduler:

update stories set IsArchived = 0 where

(Active = 1) and

(StartDate <= @currentDate) and

(EndDate is null or EndDate >= @currentDate)

and now I will just use below query in all stored procedures:

Select * from Stories where

(SiteID = @SiteID) and

(IsArchived = 0)

Does this makes sense? Will it improve performance ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

We still haven't answered the most important question.

Is this query a performance problem?

If so, have you tried the usual methods of tuning queries?

You're suggesting complicated and unusual 'solutions' to something that may not be a problem or, if it is, may be fixable in easy ways.

100 000 rows of data is not a lot. That's a very small table by the standards of what SQL Server can handle.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100961 More actions · Answer 8

Without further info, the truly best clustering index is a complete guess, but based on what is known so far, I'd say try:

Cluster the table on ( StartDate, EndDate )

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.