SQL Query timeout issue - 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

SQL Query timeout issue

Author

Topic

vishal.todkar
Starting Member

20 Posts

Posted - 2009-11-09 : 08:20:03

Hi All,
I have two databases, one is Current and another is Archive. A transaction table called “media” is available in both the databases. Child tables are available in Current database only.
When user wants to see particular date range records he can select whether to see Current database records or Archive database records.
Case 1
When he selects Current database records, the query is formed by joining “media” and child tables from Current database only and it will run on Current database. (no issue)
Case 2
When he selects Archive database records, the query is formed in such a way that “media” table from Archive database will be joined to Current database’s child tables and the query will run on Current database.
No. of records in both the database’s “media” table are 2 million.
The query runs fine in 1st case but gives SQL timeout error in 2nd case.
All the indexes are up to date and there is no fragmentation. The database owner is same for both the DBs.
Could you please help me to get a solution so that there will not be SQL query timeout error when I perform 2nd case? Thanks in advance.

Thanks & Regards
Vishal.

Vishal

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-09 : 08:22:11

let us see your query.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 10:50:28

are indexes same on both dbs media table?

vishal.todkar
Starting Member

20 Posts

Posted - 2009-11-10 : 00:46:15

Thanks for your reply..The INDEXES are same on both dbs "media" table.

Vishal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-10 : 01:20:27

We'll definitely need to see the problematic query.

Make sure your statistics are up to date as that's a very important aspect of query performance. UPDATE STATS...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

vishal.todkar
Starting Member

20 Posts

Posted - 2009-11-10 : 01:48:17

The statistics and indexes are up to date. I understood the issue but not getting solution. Please refer below query.
The user has a selection dropdown where he can select how many number of records he want to see (for example 10, 25, 50, 100, 500). Maximum is 500.
There is a navigation button where he can click on “Next” to see next set of records.

e.g. if he has selected 10 No. of records to view, and clicks on “Next”, the query will be generated so that, it will skip previous 10 records which are already displayed and displays next 10 records. Similar functionality is there for “First”, “Previous” and “Last” button click.

Below query generated when user clicks on “Next” button. “TOP 10” refers the records needs to be displayed. Subquerie's “TOP 1075468” refers records those are already displayed and should omit from next set of records.

The issue is because of inner subquery and NOT IN clause. The purpose is that main query should omit records those are already displayed (subquery records) from total qualifying records.
Some way, I need to replace this NOT IN clause. But not getting what to do. Please let me know if the topic is not clear. I am sorry for confusion.
-- Query for Archiver DB

SELECT TOP 10 agent = CASE WHEN a.firstname IS NULL THEN a.lastname + '(' + sw.name + ')' ELSE a.lastname + ', ' + a.firstname + '(' + sw.name + ')' END,
m.mediaid, m.mediatypeid,m.burntmediaid, mt.mediadesc, m.starttime, m.extension, swm.mediastoragepath, swm.enablemediasecure, m.filename, m.scfilename,
m.audiofilekeyid, m.scfilekeyid, portname, portdesc, iq.queuename AS initialqueue, tq.queuename AS takenqueue, m.agentreview, m.teamreview, m.archiveexempt,
m.filename, m.status, m.scstatus, m.audioscreensync, m.ani, m.dnis, sd.score AS cmqscore, sd.surveydataid, m.callduration, ap.name AS applicationname,
sw.switchtypeid, m.hostid, 1 AS hassurveyaccess, ics.*, 1 as canscore FROM [AQM_Archive].[dbo].media m OUTER APPLY getArchiveInteractionScoreCards(mediaid) ics
JOIN mediatype mt ON mt.mediatypeid = m.mediatypeid LEFT OUTER JOIN port p on p.portid = m.portid LEFT OUTER JOIN queue iq on iq.queueid = m.initialqueueid
LEFT OUTER JOIN queue tq on tq.queueid = m.takenqueueid LEFT OUTER JOIN iqmuser a on a.userid = m.userid
LEFT OUTER JOIN [AQM_Archive].[dbo].cmqsurveydata sd on sd.mediaid = m.mediaid LEFT OUTER JOIN switchmonitorsettings swm on swm.switchid = m.switchid
LEFT OUTER JOIN switch sw on sw.switchid = m.switchid LEFT OUTER JOIN application ap on ap.applicationid = m.applicationid
WHERE m.starttime BETWEEN '12/31/2007 18:30:00' AND '12/31/2008 18:29:59' AND ((m.status = 3 OR m.scstatus = 3))

-- Below subquery is used within the main query to skip records those are already displayed --

AND m.mediaid NOT IN (SELECT TOP 1075468 m.mediaid FROM [AQM_Archive].[dbo].media m JOIN mediatype mt ON mt.mediatypeid = m.mediatypeid
LEFT OUTER JOIN port p on p.portid = m.portid LEFT OUTER JOIN queue iq on iq.queueid = m.initialqueueid
LEFT OUTER JOIN queue tq on tq.queueid = m.takenqueueid LEFT OUTER JOIN iqmuser a on a.userid = m.userid
LEFT OUTER JOIN [AQM_Archive].[dbo].cmqsurveydata sd on sd.mediaid = m.mediaid LEFT OUTER JOIN switchmonitorsettings swm on swm.switchid = m.switchid
LEFT OUTER JOIN switch sw on sw.switchid = m.switchid LEFT OUTER JOIN application ap on ap.applicationid = m.applicationid
WHERE m.starttime BETWEEN '12/31/2007 18:30:00' AND '12/31/2008 18:29:59' AND ((m.status = 3 OR m.scstatus = 3)))
-- Order by for main query
ORDER BY m.starttime ASC, m.mediaint ASC

-- End of Query for Archiver DB

If I remove the subquery, independent of No. of millions of records in db the top 10, 25 or 500 will be displayed very fast due to appropriate indexing. But this does not solve functionality of navigation. Note that everytime user hits navigation button, the TOP X changes and thus it’s kind of dynamic query.
I have tried using table variable/temp table so that let the skip records filled in 1st and then use NOT IN to skip them from main query. it does not solve the issue instead it adds more overhead of creating and inserting, skip records in temp table or table var.

Vishal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 12:47:54

replace NOT IN with NOT EXISTS or left join and try

cvraghu
Posting Yak Master

187 Posts

Posted - 2009-11-11 : 00:33:52

If your mediaid is sequential, you can store the largest value of mediaid in current resultset in a variable and use it like below -

SELECT TOP 10 *
FROM yourtables
WHERE yourwhereclauses
AND m.mediaid > @CurrentMAXMediaID--pass it as parameter from ui
order by m.mediaid,other orderbys

Do you think this will work for you?

vishal.todkar
Starting Member

20 Posts

Posted - 2009-11-11 : 01:26:05

Thanks Guys.
I will try your suggestion on different scenarios and will let you know.
Meantime I am trying using ROW_NUMBER() and CTEs. I will post these queries soon. I appreciate your feedback and response.

Vishal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-11 : 11:59:59

good luck!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources