Performance problem and looping

I posted this to SQL Team.com and I thought I'd repost it here and get the .Net perspective.

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 completely unacceptable. 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!

-- JeffCan you post the SQL that's sent? Is it sent once or multiple times?|||Is the data the same on the customer's server? If there are 10 rows in your local tables and 100,000 rows on the customer's, of course you may get different results. Try copying and pasting each statement that is captured in Profiler into Query Analyzer and executing on your customer's SQL Server to isolate which one is slow. This will help you figure out if the query needs to be optimized.

In the customer's environment, is the ASP.NET app executing on the same machine or another web server? If there is a slow network connection between the two, that could also explain why it runs fast locally, but slow in the other environment.

In general you want to avoid looping and executing statements over and over again. This will usually eat up CPU on the database server.

John|||I sent the concantenated series of queries below (sorry for the mess). Originally, I was sending them individually and thought the roundtrip might be slowing things down, but in both cases (concatenated or not) the result is the same.

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, sto_internal_name, sto_bizflag, ABRA_STORE_SUM.* INTO ABRA_SUMS FROM ABRA_STORE_SUM, ABRA_MAX, ABRA_STORE_LOAD, ABRA_CAPLOAD, ABRA_STORE, ABRA_DISTRICT WHERE store = sto_id AND ds_date_load = maxdate AND sto_id = st_sto_id AND st_id = ds_st_id AND sto_clm_id = cl_clm_id AND st_cl_id = cl_id AND sto_dst_id = dst_id AND dst_id = '1';SELECT sto_id, sto_internal_name, sto_bizflag, SUM(ds_st_quantity_target) AS total_quantity_target, SUM(ds_ins_count) as total_actual_count,SUM(ds_st_wip_target) AS total_wip_target, SUM(ds_ins_amt) as total_ins_amt, SUM(ds_ins_count) as total_ins_count,SUM(ds_sch_amt) as total_sch_amt, ds_date_load as last_update INTO ABRA_TOTALS FROM ABRA_SUMS GROUP BY sto_internal_name, ds_date_load, sto_id, sto_bizflag;ALTER TABLE ABRA_TOTALS ADD total_actual_amount AS total_ins_amt + total_sch_amt;

Thanks,

-- Jeff|||John,

The customers database is larger by a factor of two.

I should have mentioned I ran the queries on the customer site in Query Analyzer and get a 10 second response time for the batch. One query in particular takes the majority of time and could be optimized but the QA different performance from the 2 minutes when running from a web server.

Both the app and the server are on the same machine, so I don't think the network is an issue unless there's some problem going back to the client side.

I'm not deliberately looping, it appears to happen on it's own.

Thanks,

--Jeff|||1. It's a nasty mix of DDL and DML is there any way you avoid that? Do you really need to drop a table and add columns afterwards? Can't you create the table and at least truncate it? Could a temp table be used?

2. Have you tried running the batch from a Query Analyser on the web server?

3. I can't see any obvious reason for any kind of looping unless you have some kind of retry mechanism somewhere?