performance loss between sql-and web server ?!? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

performance loss between sql-and web server ?!?

hi. i’ll try to keep it as short and pregnant as possible (as far as my english allows me to do). following problem. i have an user defined function (mssql) with lots of parameters and joins over a dozen(+) tables. Parameter Set / time for execution in sec./ number of returned rows A | 5.0s | 2000 rows (executed in query analyzer)
A |25.0s | 2000 rows (executed in script)
B | 4.0s | 500 rows (executed in query analyzer)
B | 5.0s | 500 rows (executed in script)
ok, i thought, nobody needs these 2000 results displayed at once.
i made the connection between sql server and web server responsible for this huge time difference. so i just tried a TOP 50 on the query, hoping (and man, i was 100% sure it would work…) that the execution time now decreases. however this is the outcome Parameter Set / time for execution in sec./ number of returned rows A | 5.0s | 50 rows (executed in query analyzer)
A |25.0s | 50 rows (executed in script) No change at all in the execution time of the script.
Why is that ??? Thanks for your help, peace, sdiwi.

http://dict.leo.org/?lp=ende&lang=de&searchLoc=0&cmpType=relaxed&relink=on§Hdr=on&spellToler=std&search=pregnant<br /><br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />] scnr<br /><br />Any more informations as to what your query looks like and things like that?<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
Have you tested the execution plans during this processes.
It also depends on number of factors such network, indexes, type of connection between web & SQL Server and search arguments. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
It could be that you order by column that is not indexed, so the whole set has to be retrieved before returning top 50 rows. The reason could be something else, it’s hard to know without more info.
how much data is moving across the network
what is the network library being used in the script
what is the status of set nocount
Thanks for your replies. Sorry for replying that late, but I wasn’t notified about any new postings via mail (maybe this f****** mail server at my f***** workplace eats the mails from this domain).
Hmm. I think i MIGHT have found the solution (although i cannot verify…).
IIS and MS-SQL are running on the same computer. I think it has to do something with my problem. I wrote a client based java application, that connects to the db and executes the same udf (as in my postiong above).
It’s executed in the same time as in the query analyzer, whereas the server-side asp script still takes more than 5 times longer… Brb. So my prob has nothing to do with the query itself. It seems to be a hardware problem ?!? Or maybe changing some configuration data might help (because when using parameter set B, the script is only slightly slower than the query analyzer…) –
maybe in case (A) the data is sent in several little steps, and in case (B) all data is being sent at once. any suggestions? thanks alot…. peace, sdiwi. @mmarovic
the TOP statement in my query has absolutely no use for performance. i just tried TOP because i thought my asp-script would waste all the time processing 2000 results.
then i just picked the top 50 results, but that didn’t accelerate my script… @joechang
Q:how much data is moving across the network
– A: not much – approx 1MB (max)
Q:what is the network library being used in the script
– A:ADODB connection
Q:what is the status of set nocount
– A: It is turned off. Turning it on however has no effect on the execution time – neither in my script nor in my query analyzer… @satya
u asked me wheter i had a look on the execution plan.
umm. yes – i just did. but i don’t know how to read it. if it would’nt be over 3 screens or sth., i’d post it here…
actually my query is not slow.
but i can’t figure out what IS slow… @Frank
the link you posted doesn’t work for me. first i didn’t know what the abbreveation "scnr" meant – when i found out, i suddenly felt keen interrest growing on what you really wanted to tell me.
BTW can you confirm the memory assigned to SQL server.
Check this relevanthttp://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1941 thread for any clue. For more information on frank’s link just cut and paste on the browser [8D] Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />@Frank<br />the link you posted doesn’t work for me. first i didn’t know what the abbreveation "scnr" meant – when i found out, i suddenly felt keen interrest growing on what you really wanted to tell me.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Oh, nothing really smart. Just tried to have some fun on your use of "pregnant" [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
@frank<br />what’s so wrong on my use of pregnant?<br />i know what it actually means, but you know, at school our teachers always used this word as a synonym for succinct/concise (but probably just because of the similarity to the german "prägnant"). <br />LEO even tells me that pregnant can be translated with "bedeutungsvoll" (meaning significant ), what means, i may even be right <img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />…<br /><br /><br />@satya<br />thanks for telling me how to use my browser. <img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /><br />the link however didn’t work for me. but the mystery just has been unraveled…<br />i’ll check out the topic you posted – thanks.<br /><br /><br /> <br />if however someone has any idea how i could solve my problem / what is the cause for my prob – feel free to post <img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /><br /><br /><br />peace,<br />sdiwi.
Confirm the SQL memory settings and authentication used to connect web & SQL.
Also check the TEMPDB contention on SQL Server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>