November 8, 2010 at 7:18 am
Hi Guys,
I've got a query that is held in a function. the function is called as per below.
SELECT dbo.funQPIQuotesHhi('08 Nov 2010','01 Nov 2009 00:00','30 Nov 2009 23:59')
In ssms it takes fractionally over 1 second to run. when the exact same query is called through a PHP page, it takes 2 minutes. Nothing other than this 1 query is being called and the sqlserver.exe process is sat at 97% cpu usage for the whole time it's processing.
it sometimes errors with 'query failed' through php too.
I'm calling it like this:
$dbConn = mssql_pconnect(DBSERVER, DBUSER, DBPASS);
$sql = " SELECT dbo.funTotalQuotesHhi('" . $glDate->shortDate() . "','" . $repDate->shortDate() . " 00:00','" . $repEndDate->shortDate() . " 23:59')";
$dbRecord = mssql_query ($sql, $dbConn);
$months[$i]['TQ'] = mssql_result ($dbRecord,0,0);
I know this isnt a php forum... but has anyone else here come accross such odd behaviour before?
Cheers,
Ben
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
November 8, 2010 at 7:24 am
Run profiler, see what the exact call is that PHP is making to SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 7:32 am
OK, done that.
Trace Start2010-11-08 14:29:09.033
SQL:BatchStartingSELECT dbo.funTotalQuotesHhi('08 Nov 2010','01 Nov 2009','30 Nov 2009')
Microsoft SQL Server Management Studio Express - QuerywardbFORTIS-UK\wardb5336542010-11-08 14:29:16.793
SQL:BatchCompletedSELECT dbo.funTotalQuotesHhi('08 Nov 2010','01 Nov 2009','30 Nov 2009')
Microsoft SQL Server Management Studio Express - QuerywardbFORTIS-UK\wardb110978843012815336542010-11-08 14:29:16.7932010-11-08 14:29:18.073
SQL:BatchStarting SELECT dbo.funTotalQuotesHhi('08 Nov 2010','01 Nov 2009','30 Nov 2009')PHP 5KPIuser9924572010-11-08 14:29:24.010
SQL:BatchCompleted SELECT dbo.funTotalQuotesHhi('08 Nov 2010','01 Nov 2009','30 Nov 2009')PHP 5KPIuser482659400939801497139924572010-11-08 14:29:24.0102010-11-08 14:30:13.717
Trace Stop2010-11-08 14:30:39.293
I think it might relate to indexing... do I have to give the KPIuser account permission to use indexes?
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
November 8, 2010 at 7:34 am
urgh that does not format well...
basically the query is the same but
cpu goes from 1109 to 48265
reads goes from 78843 to 9400939
writes goes from 0 to 801
duration increases by about 40.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
November 8, 2010 at 7:36 am
BenWard (11/8/2010)
do I have to give the KPIuser account permission to use indexes?
No.
Edit and change to code:plain, it'll look better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 7:40 am
Could be related to different SET options, though I don't know how to tell what the default options are for a connection from PHP (ansi nulls, arith abort, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 7:42 am
ok ta.
I'll try calling the func through a SP and running that from php and see what happens.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
November 8, 2010 at 7:58 am
Very likely exactly the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 2:15 pm
What is the function actually doing? Knowing the underlying code and schema would help find a possible issue.
Also if you trace the SQL statements, not only the batch you can see within the function where the extra reads are comming from.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 9, 2010 at 4:04 am
gosh....
whats going on here... ran in this morning and it takes 54 seconds on both ssms and php!
ugh never mind. looks like I'll have to prepopulate data sets over night instead of generating reports on the fly.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply