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 |
|
JLatiolait
Starting Member
6 Posts |
 Posted - 2005-02-22 : 15:07:59
|
| Hello,I'm running a compound SQL statement from an ASP page that accomplishes DELETE and INSERT opertations on 2 tables. This operation is to be performed by Network Admin. only. The "entire" query below takes appx. 12 sec. via Query Analyizer (appx. 3000 records involved)Problem - from ASP page, a part of the query is responsible for timing out the server (setting: CommandTimeout = 300). When the query is run in fragments, the rest of the query runs fine from ASP....Problem Part of Query Is:DELETE from credit where cdt_hold = 0 AND cdt_dateUpdate < GETDATE() - 3[Error on ASP page: Microsoft OLE DB Provider for SQL Server (0x80040E31)Timeout expired][Executes in 7 sec. via QA]NOTE: When I look at the QA / Estimated Execution Plan it shows 46% of the query is deleting the index on the PK.* Why is this SQL statment taking so long submitteed via ASP and not QA? Is there a better way in SQL to delete so many (3000) selected values that meet criteria from the table?Assistance is greatly appreciatd... ?Entire Query (jsut for refference)-DELETE FROM XLImport3 INSERT INTO XLImport3 SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\BCA.xls;Extended Properties=Excel 8.0')...[Sheet1$] DELETE from credit where cdt_hold = 0 AND cdt_dateUpdate < GETDATE() - 3 INSERT INTO credit (cdt_accNum, cdt_Name, cdt_terr, cdt_whs ) select * from XLImport3 where ACCT NOT IN (select cdt_accNum from credit) |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-23 : 00:02:08
|
| I presume the DELETE FROM XLImport3 INSERT INTO XLImport3 SELECT * FROM OPENDATASOURCE('Microsoft ...is putting back most of the rows that were there originally?At the cost of considerably more [development] effort you could build a query to bring the Excel file intoa temporary table and then selectively insert/update/delete the XLImport3 table. This would only be worthwhile if there is a lot of data, but I expect that isn't the case as Excel files can't be THAT big, can they?Frequently doing DELETE / INSERT INTO may muck up the statistics on the table. Might be worth doing an UPDATE STATISTICS on the table after the INSERTHas the database got set to AutoClose? That might explain why ASP is taking longer - QA would hold the database open, ASP would open/close it on every access.Kristen |
 |
|
|
JLatiolait
Starting Member
6 Posts |
Posted - 2005-02-23 : 23:07:44
|
| Hi Kristen,Thank you very much for your reply! for you and anyone who ever stumbles accross a similar problem...Take anohter look at your ASP and make sure it's correct. This is where my problem was! Stupid. It was not in the SQL or on SQL Server at all.Thanks Again. |
|
|
|
|
|
|
|
|
ASP/SQL Delete of 3000 records - timeout