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.
| Author |
Topic |
|
kimbelj
Starting Member
13 Posts |
 Posted - 2004-08-19 : 12:00:14
|
| 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_MAXexec sp_reset_connectionDROP TABLE ABRA_SUMSexec sp_reset_connectionSELECT N'Testing Connection...'EXECUTE msdb.dbo.sp_sqlagent_get_perf_countersSELECT 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
38200 Posts |
Posted - 2004-08-19 : 14:31:53
|
| 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 |
 |
|
|
kimbelj
Starting Member
13 Posts |
Posted - 2004-08-19 : 16:08:56
|
| 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 |
|
|
|
|
|
|
|
|
Performance problem with .NET application