SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Performance problem with .NET application
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kimbelj
Starting Member

13 Posts

Posted - 08/19/2004 :  12:00:14  Show Profile  Reply with Quote
I apologize in advance if this is the wrong forum. I'm a developer and not too much SQL Admin knowledge (and neither does my customer) but this problem seems to have something to do with the database server. I'm not sure what might be good information that might point to the problem but this is an overview:

The application is a ASP.Net (ADO.Net) application which sends an embedded SQL query to the server. I can run the application on my local development server and get a two second response time. Running on the customer's server (faster CPU, etc) at night with no traffic takes upwards of two minutes which is a completely unacceptable response time. I ran SQL Profiler (is it possible to post the trace?) and see that it chugs along first getting the query then repeating it over and over. The query consists of a concantenation of queries something like:

DROP TABLE ABRA_MAX;DROP TABLE ABRA_SUMS;DROP TABLE ABRA_TOTALS;SELECT st_sto_id AS store, MAX(ds_date_load) AS maxdate INTO ABRA_MAX FROM ABRA_STORE_SUM, ABRA_STORE, ABRA_CAPLOAD, ABRA_STORE_LOAD WHERE sto_clm_id = cl_clm_id AND st_cl_id = cl_id AND ds_st_id = st_id GROUP BY st_sto_id;SELECT sto_id, etc.
....

In the profiler it appears the server runs this with a duration of approximately 10 seconds then starts over again breaking apart the queries individually separated by "exec sp_reset_connection" events:

DROP TABLE ABRA_MAX
exec sp_reset_connection
DROP TABLE ABRA_SUMS
exec sp_reset_connection
SELECT N'Testing Connection...'
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
SELECT st_sto_id AS store, MAX(ds_date_load) AS maxdate INTO ABRA_MAX FROM ABRA_STORE_SUM, ABRA_STORE, ABRA_CAPLOAD, ABRA_STORE_LOAD WHERE sto_clm_id = cl_clm_id AND st_cl_id = cl_id AND ds_st_id = st_id GROUP BY st_sto_id
...

It then repeats this cycle a number of times reexecuting the 10 second query over and over while chugging along through the other queries. I don't see anything like this on my development server using the profiler it buzzes right through it.

Hopefully, I'm doing something stupid and there's a quick fix - any pointers are appreciated. I know that I really should be using a stored procedure (older and wiser now) - would this make a big difference?

Thanks in advance!


tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/19/2004 :  14:31:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Using a stored procedure would certainly help, but that doesn't explain why it is looping to run these over and over again. SQL Server doesn't do this on its own, so I would suggest running your application in debug mode on the customer's system. You could then step through your code to figure out what is going wrong.

I do have some questions though. Why are you dropping tables? Do you have the appropriate indexes on the tables that you are querying? For the child tables, are the foreign keys indexed? It is common mistake to think an index is automatically created when a foreign key is created. This occurs for primary keys but not foreign keys.

Tara
Go to Top of Page

kimbelj
Starting Member

13 Posts

Posted - 08/19/2004 :  16:08:56  Show Profile  Reply with Quote
Thanks, I did consider that it could be looping on the application side, but I didn't see the same behavior on my development system and discounted that. I thought perhaps there might be some timeout mechanism in SQL Server that causing it to reattempt the queries (showing my ignorance) so it's good to know something like that isn't going on. I will take your advice and see if I can debug onsite (somewhat painful).

The dropped tables are temporary tables. I made them regular tables since I was having trouble with persistance and at the time this was the path of least resistance. There are no indexes (except for primary keys) which certainly would help, thanks.

-- Jeff
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000