performance 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

performance issue???

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-13 : 20:38:21

I need help troubleshooting a time out when running a stored procedure.
I have 2 stored procedures: dealerpackage, dealerpackageperProvince

dealerpackage: counts the total packages (THIS ONE FAILS TIMEOUT)
dealerpackageperCity: counts the total packages per province (THIS ONE WORKS)

These 2 sp are pretty similar and have 5 tables joining each other. They are joined in the following order. The size of the tables are:

row count:
13923661
17562
2
3
2620997

I'm no sure why I get time out error. Even though I run it manually, it's lightning fast (< 1 min)
Any suggestion will be appreciated.

error:
Error retrieving count. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 00:01:31

sorry we need more information. are there sps parameter driven? is the same code running fast when executed outside?

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

Kristen
Test

22859 Posts

Posted - 2012-01-14 : 05:48:29

"I'm no sure why I get time out error. Even though I run it manually, it's lightning fast (< 1 min)"

Probably a difference between the query you are running "manually" and the one from the application. Or your "manual" one is cached, and the application one isn't. Or your application is retrieving a lot of rows and the large size of the data, and the slow speed of the bandwidth, is causing the timeout - seems unlikely for a single count (if that is the result you are expecting - just one count?)

Use SQL profiler to see EXACTLY what the query is that is sent from the application and try than "manually".

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-16 : 04:53:16

Visak,
Its not a parameter driven. It's a report from reporting services where I can pick a date range. The date range is usually one day of data like (from 12-12-12 to 12-13-12).
I dont understand the second question. But It's fast when I execute it on sql server management studio manually.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-16 : 04:56:37

kristen,
it's not one field count. it's multiple counts for multiple packages and provicnces, so several rows and columns.
I will try the sql profiler first. thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-16 : 04:58:57

< 1 minute is not fast, that's terribly slow. Timeout settings are usually 30 seconds by default (in .Net anyway)

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 05:27:10

quote:
Originally posted by basicconfiguration

Visak,
Its not a parameter driven. It's a report from reporting services where I can pick a date range. The date range is usually one day of data like (from 12-12-12 to 12-13-12).
I dont understand the second question. But It's fast when I execute it on sql server management studio manually.

in reporting services how will it pass value for one day date range? isnt it done through parameters? is filtering done at report or database level?

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

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-16 : 17:11:24

V,
By using 2 parameters @from and @to. Filtering its done in the stored procedure (database level)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-16 : 17:51:07

quote:
Originally posted by basicconfiguration

V,
By using 2 parameters @from and @to. Filtering its done in the stored procedure (database level)

it's probably time to post the code. Probably will be some red flags that will be fairly quick to spot with a trained eye.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-01-17 : 02:51:03

Transact_Charlie,
I think I found out the problem. But I will confirm tomorrow. My theory is that 2 tables need indexes LOL.

I put the code here if it helps:

SELECT (
SELECT
APICount.Name,
APICount.SiteID,
APICount.TestID,
APICount.TestGroupID,
APICount.GroupName,
'integer' AS 'DataType',
COUNT(1) As 'Value'
FROM
(SELECT
EmailLog.SiteID,
Test.TestID,
TestGroup.TestGroupID,
TestGroup.GroupName,
CompanyXML.CompanyDetail.value('(/Company/CompanyAttributes/CompanyAttribute[@Type="DSSPackage"]/@Default)[1]', 'VARCHAR(30)') AS Name
FROM
ComList.EmailLog WITH (READUNCOMMITTED)
JOIN dbo.Test WITH (READUNCOMMITTED)
ON Test.TestID = Attribute.value('(/MVTs/MVT/TestID)[1]', 'INT')
JOIN dbo.TestGroup WITH (READUNCOMMITTED)
ON TestGroup.GroupName = Attribute.value('(/MVTs/MVT/TestGroup)[1]', 'VARCHAR(30)')
JOIN ComList.AdXML WITH (READUNCOMMITTED)
ON EmailLog.ForeignID = AdXML.AdForeignID
AND EmailLog.SourceID = AdXML.AdSourceID
JOIN ComList.CompanyXML WITH (READUNCOMMITTED)
ON AdXML.CompanyForeignID = CompanyXML.ForeignID
AND AdXML.CompanySourceID = CompanyXML.SourceID
WHERE
( EmailLog.EmailTypeID = 1789)
AND (EmailLog.EmailBody NOT LIKE ('%xml%'))
AND (EmailLog.EmailDate BETWEEN @StartDate AND @EndDate)
AND CompanyXML.CompanyDetail.value('(/Company/CompanyAttributes/CompanyAttribute[@Type="DSSPackage"]/@Default)[1]', 'VARCHAR(30)') IS NOT NULL
AND CompanyDetail.value('(/Company/Contacts/Contact/Location/@Province)[1]', 'VARCHAR(30)') = @Province
)AS APICount
GROUP BY
APICount.SiteID,
APICount.TestID,
APICount.Name,
APICount.TestGroupID,
APICount.GroupName
FOR XML AUTO, TYPE, ROOT('Counters')
--------------------------------- --------------------------------- --------------------------------- ---------------------------------

SELECT (
SELECT
APICount.SiteID,
APICount.TestID,
APICount.TestGroupID,
APICount.GroupName,
APICount.Name,
'integer' AS 'DataType',
COUNT(*) As 'Value'
FROM
(SELECT
EmailLog.SiteID,
Test.TestID,
TestGroup.TestGroupID,
TestGroup.GroupName,
--'EmailLeads' As Name,
CompanyXML.CompanyDetail.value('(/Company/CompanyAttributes/CompanyAttribute[@Type="DSSPackage"]/@Default)[1]', 'VARCHAR(30)') AS Name
FROM
ComList.EmailLog WITH (READUNCOMMITTED)
JOIN dbo.Test WITH (READUNCOMMITTED)
ON Test.TestID = Attribute.value('(/MVTs/MVT/TestID)[1]', 'INT')
JOIN dbo.TestGroup WITH (READUNCOMMITTED)
ON TestGroup.GroupName = Attribute.value('(/MVTs/MVT/TestGroup)[1]', 'VARCHAR(30)')
JOIN ComList.AdXML WITH (READUNCOMMITTED)
ON EmailLog.ForeignID = AdXML.AdForeignID
AND EmailLog.SourceID = AdXML.AdSourceID
JOIN ComList.CompanyXML WITH (READUNCOMMITTED)
ON AdXML.CompanyForeignID = CompanyXML.ForeignID
AND AdXML.CompanySourceID = CompanyXML.SourceID
WHERE
(EmailLog.EmailTypeID = 1789)
AND (EmailLog.EmailBody NOT LIKE ('%xml%'))
AND (EmailLog.EmailDate BETWEEN @StartDate AND @EndDate)
AND CompanyXML.CompanyDetail.value('(/Company/CompanyAttributes/CompanyAttribute[@Type="DSSPackage"]/@Default)[1]', 'VARCHAR(30)') IS NOT NULL
--AND (EmailLog.EmailDate BETWEEN '2011-10-14' AND '2011-11-15')
)AS APICount
GROUP BY
APICount.SiteID,
APICount.TestID,
APICount.Name,
APICount.TestGroupID,
APICount.GroupName

ORDER BY
APICount.SiteID,
APICount.TestID,
APICount.TestGroupID,
APICount.GroupName,
APICount.Name

FOR XML AUTO, TYPE, ROOT('Counters')
) AS APICount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources