October 16, 2008 at 10:13 pm
Hi,
I would like to estimate the amount of time for running a query. It involve inserting X amount of records into a backup DB from a source DB. After that delete these inserted records from the source DB.
When I ran this query on development environment the query returned with 22057 records inserted and deleted within 1 minute.
I queried the actual production database and it have close to 7 million records. What would be the estimate amount of time for the query to complete this time?
e.g. of query:
-- insert record into backup DB for records more than 3 months
insert into backup.dbo.users
select userid from source.dbo.users where getdate()-stime>=89
-- delete record from source
delete from source.dbo.users
where userid in (select userid from backup.dbo.users)
October 17, 2008 at 5:36 am
It really depends on ur server and machine cofiguration and speed. If you have 7 million record in a table then of course ur organization is haivng server with good speed. From ur analysis if 22000 record takes around 1 minute then 7 million takes long time. But at first poing i dont think 22000 record can takes around 1 minutes. it should take less than second. U need to give indexing if ur query is big and having joins etc.
October 17, 2008 at 5:55 am
There's no real way to know this. If the same database you have in production was on a machine, all by itself, with no users, it might take 1 minute to delete 7 million records. But in production you're going to have other users running queries, let's assume for a moment, not against the table you're trying to delete from, that are using up CPU, memory and disk I/O. Now the identical query against identical data takes longer. Then, you have to take into account that other users are inserting or deleting against the table. You have to wait for their transactions to clear before you can delete the data. It just got longer. If you also have users reading the data, you may have to wait for that to clear as well. Once again, it got longer.
Estimating time for execution is a pretty bad metric. Estimating cost, now that's something you can do. Take your query and get an estimated execution plan. That will show you the indexes, joins, etc., that are likely to be used by the query. If there are problems with the estimated plan, then you can anticipate the same kinds of problems with the actual execution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2008 at 1:16 am
Thanks for all the info.. :hehe:
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply