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.
| Author |
Topic |
|
raghav_sai2002
Starting Member
18 Posts |
 Posted - 2009-11-24 : 03:36:13
|
| HI,i have written a query for which i am able to retrieve the data, but some users are complaining its too slow.when i executed it, in the Database, it is taking around 45-50 secs and retrieving about 73000 records. the same thing when i execute from the APP ( .net based, regular sql connection ), it is taking about 3mins to show the open dialogue box and another 3 mins to actually open the file. it is totally taking like 6-7 mins.so i was wondering if we can tune this query/***********************************/SELECT a.*FROM (SELECT a.*, (CASE WHEN Isnull(a.visits,0) = 0 THEN 0.00 ELSE (Isnull(a.conversions,0) / a.visits) END) AS conversion_rate FROM (SELECT b.brandname + ' > ' + c.campaignname AS campaign, d.channelname AS channel, (CASE a.subchannelid WHEN '000' THEN 'Natural Search' ELSE e.subchannelname END) AS sub_channel, a.keyword_category AS keyword_category, a.keyword AS keyword, a.keywordsearched AS keyword_searched, a.matchtype AS match_type, a.ROUTE AS ROUTE, a.route_destination AS route_destination, g.naturalrank AS natural_rank, Sum(a.visits) AS visits, Sum(a.revenue) AS revenue, Sum(a.quantity) AS conversions FROM tblkeywords a WITH (NOLOCK) LEFT OUTER JOIN tblbrands b WITH (nolock) ON a.accountid = b.accountid AND a.brandid = b.brandid LEFT OUTER JOIN tblcampaigns c WITH (nolock) ON a.accountid = c.accountid AND a.brandid = c.brandid AND a.campaignid = c.campaignid LEFT OUTER JOIN tblchannels d WITH (nolock) ON a.channelid = d.channelid LEFT OUTER JOIN tblsubchannels e WITH (nolock) ON a.channelid = e.channelid AND a.subchannelid = e.subchannelid LEFT OUTER JOIN tblrankdata g ON a.programid = g.programid AND Convert(VARCHAR(10),a.dtcampaignvisit,111) = Convert(VARCHAR(10),g.dttrack,111) AND a.channelid = g.channelid-- AND a.accountid = g.accountid AND a.keyword = g.keyword WHERE (a.accountid = '91289') AND a.dtcampaignvisit BETWEEN '11/01/2009' AND '11/22/2009' GROUP BY b.brandname + ' > ' + c.campaignname, d.channelname, (CASE a.subchannelid WHEN '000' THEN 'Natural Search' ELSE e.subchannelname END), a.keyword_category, a.keyword, a.keywordsearched, a.matchtype, a.ROUTE, a.route_destination, g.naturalrank) a) a /***********************************/the table tblkeywords has got millions of records and it is the one that produces 73000 records for that account between that time range.and the next one that is taking time in the query is "tblrankdata".both the table has got indexes ...for tblkeywords -- one index on the columns [programid, dtcampaignvisit , channelid, subchannelid, keyword_category, keyword}and in the second table,( [ProgramId] ASC, [dtTrack] ASC, [ChannelId] ASC, [AccountId] ASC)now as soon as i add the second table in the join the processing time increased by 20 secs... so can i improve this query? please help me out on this on, thank you? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-11-24 : 04:11:58
|
| Can you post the (actual or expected) execution plan? This will be the true definition of the problem.This bit is NOT helping you. Functions GENERALLY prevent INDICES being used. What is it meant to do as a filter/join condition...in non-technical terms?"Convert(VARCHAR(10),a.dtcampaignvisit,111) = Convert(VARCHAR(10),g.dttrack,111)"Also have you got useful indices on the other tables named? |
 |
|
|
raghav_sai2002
Starting Member
18 Posts |
Posted - 2009-11-24 : 09:12:49
|
| Hi,Thank you,1. i agree with the fact that functions like this are time consuming "Convert(VARCHAR(10),a.dtcampaignvisit,111) = Convert(VARCHAR(10),g.dttrack,111)" but the problem is that the dtcampaignvisit and dttrack are datetime field.. i wanted to have the data which has got the same date.. this will fail if i don't use this... is there a better way to perform this efficiently...i tried to copy the actual execution plan from my server but for some reason it wasnt allowing me, in the actual plan 95% of the time is taken for the last nested join ( i.e the join between tblrankdata and tblkeywords ) ... and the rest of the time is taken for sorting and other operations.thank you very much |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2009-11-24 : 09:53:12
|
Raghav,I think you were on the right track with:AND a.dtcampaignvisit BETWEEN '11/01/2009' AND '11/22/2009' So why not try changing:AND Convert(VARCHAR(10),a.dtcampaignvisit,111) = Convert(VARCHAR(10),g.dttrack,111)to:AND g.dttrack BETWEEN '11/01/2009' AND '11/22/2009' Is a.accountid indexed?Just a few things regarding your code being a little hard to read on this board:- Indent your code if you are not doing so, if you are, when you post it use the 'code' tags with the '#' button- Try to use more meaningful Table Placeholders - Ex: CHAN for tblChannels instead of D, you wouldn't want to use something like this in C#: public ChannelClass D; - But good use of putting things on separate lines |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-11-24 : 12:01:23
|
I think casting dates to strings is quite slow.According to this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35296The general way to strip time is to do this:SELECT dateadd(d, datediff(d, 0, getdate()), 0) Where you obviously replace the GETDATE() call with your columns.Not sure if that would be any quicker than DATEDIFF though. I'm sure it would be a lot quicker than the CONVERT(CHAR.......The best way would be to create two precomputed columns on those tables and just store the date portions of the columns you are matching on (still as DATETIME!)Then you can use a simple equivalence and get use out of an index on those columns,Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Need help in performance tuning of the query