Avoid same "where" clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Avoid same "where" clause

Author

Topic

abhijeetdighe
Starting Member

24 Posts

Posted - 2012-04-13 : 00:05:06

Hi,

I have following type of queries in my stored procedure:

select * from tab where col1='a' and col2='b'
select * from tab where col1='a' and col3='c'
select * from tab where col1='a' and col4='d'
select * from tab where col1='a' and col5='e'

In above queries, col1='a' condition is same for all the queries. So for all the queries this condition is checked multiple times. How can I avoid this considering performance, as size of tab is large I don't want to check that repeatedly.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-13 : 01:23:10

Please post some sample data so that we know what result you are expecting.
By what I understand from your post. Can you alter the query like this?. Try it:


select * from tab where col1='a' and (col2='b' OR col3='c' OR col4='d' OR col5='e')

N 28° 33' 11.93148"
E 77° 14' 33.66384"

abhijeetdighe
Starting Member

24 Posts

Posted - 2012-04-13 : 01:58:58

On our home page we have 4 different sections which are pulling data from 4 different stored procedures. But the data from same table which is "Articles" is being shown in these sections. Articles table is quite large which contains data of all our clients, so WebsiteID column is used in each query. What I am planning is to combine all 4 different procedures into a single procedure and return multiple result sets for different sections of our site.

Following are sample queries which I will place in single procedure. I need to keep these queries separate to show result in different sections. As you can see in every query "WebsiteID = 1" is checked multiple times to fetch the data from same big table "Article".
How can I avoid checking same condition multiple times in same table?

select * from Articles where WebsiteID = 1 and Column2 = 'True'
select * from Articles where WebsiteID = 1 and Column3 = 'True'
select * from Articles where WebsiteID = 1 and Column4 = 'True'
select * from Articles where WebsiteID = 1 and Column5 = 'True'

Above queries are just sample, they contain other conditions or subquries as well. My main aim is to avoid checking WebsiteID column each time in the big table "Articles".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-13 : 03:13:56

SELECT * FROM dbo.Articles WHERE WebSiteID = 1 AND True IN (Column2, Column3, Column4, Column5)

N 56°04'39.26"
E 12°55'05.63"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-13 : 04:25:35

Yes. Sweposo beat me to it. I had the same query.

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

abhijeetdighe
Starting Member

24 Posts

Posted - 2012-04-13 : 06:59:55

As I said earlier, these are just sample queries and they contain other multiple conditions or subquries as well. I need 4 different queries returning 4 different result sets to show on the home page. Articles table is quite big and each time WebSiteID is checked, Is there other way considering performance?

quote:
Originally posted by SwePeso

SELECT * FROM dbo.Articles WHERE WebSiteID = 1 AND True IN (Column2, Column3, Column4, Column5)

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-13 : 08:13:57

Yes, if you show us the full scenario.

N 56°04'39.26"
E 12°55'05.63"

abhijeetdighe
Starting Member

24 Posts

Posted - 2012-04-13 : 11:11:51

Here is the common part from all of the quries. You can see that WebsiteID, StartingDate, EndingDate, IsActive flags are checked in all the 4 queries. How can I avoid this multiple times checking in the same procedure when retrieving data from the same large table?

select * from Article where WebsiteID = @WebsiteID and StartingDate >= @CurrentDate and EndingDate <= @CurrentDate and IsActive = 'True' and ---------(other conditions)

Above is the main criteria which is checked first and which is common and then according to the way the data need to be presented, I need to then use other "where" conditions, joins, etc. What I am thinking is there should be some way so that one basic query can be written for common part and then I can filter that data as per the need. Ex. like using temporary memory tables (I am not sure about performance). What will be the best approach to this issue considering performance?

quote:
Originally posted by SwePeso

Yes, if you show us the full scenario.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:47:37

if the additional logics involve lot of joins and complex conditions then its probably worth putting common resultset initially in temporary table and then then join from temporary table for rest of conditions. This will make sure you're working with only required subset of data as determined by your selection of common parameters for rest of cases. You can further speed up performance by adding required indexes to temporary table created based on columns used in the join and where condition.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources