| Author |
 Topic  |
|
|
abhijeetdighe
Starting Member
India
24 Posts |
 Posted - 04/13/2012 : 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.
|
Edited by - abhijeetdighe on 04/13/2012 07:00:32
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 04/13/2012 : 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
India
24 Posts |
Posted - 04/13/2012 : 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".
|
Edited by - abhijeetdighe on 04/13/2012 02:05:45 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/13/2012 : 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
India
227 Posts |
Posted - 04/13/2012 : 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
India
24 Posts |
Posted - 04/13/2012 : 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"
|
Edited by - abhijeetdighe on 04/13/2012 07:00:56 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/13/2012 : 08:13:57
|
Yes, if you show us the full scenario.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
abhijeetdighe
Starting Member
India
24 Posts |
Posted - 04/13/2012 : 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"
|
Edited by - abhijeetdighe on 04/13/2012 11:12:33 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 04/13/2012 : 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/
|
|
|
| |
Topic |
|
Avoid same "where" clause
