SQL How fast is my file importing into my database?

Ok, so i don't know if this is even possible. Not even sure how to ask exactly but i'll give it a try. What i am doing is importing files into a database. The files takes about an hour to import. I know what table is being updated. What i want to know is how fast is that data importing. Is there a way to find the time from the last row updated to the next row updated? The primary key is auto incremented if that helps at all. unfortunately no datetime stamp in the table. Thanks for any help you can give.
jacobJLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
you could run select count with nolock on the target/destination table to see how much was allready imported as I assume you know by doing the same how much needs to be imported.
But mandatory use the NOLOCK hint like

SELECT COUNT(*) FROM YourTable WITH(NOLOCK);

or use SSMS SQL own "Disk usage by top tables" report - just rightclick in SSMS the DB name and select Reports-.Standard reports->Disk usage by top tables
1
jacobJLAuthor Commented:
lcohon thanks for quick response. I know how to select the count. But i don't know how many records need to be imported. Even if i did i am not sure exactly how that will tell me exactly how fast each record is being loaded into the database. You understand what i mean? for instance i can continually select the count and see the row number increase by 100 or more records at a time. But was hoping for a more accurate way to tell the average speed the rows are increasing.
0
lcohanDatabase AnalystCommented:
"... more accurate way to tell the average speed the rows are increasing."
there's no such thing simply because....INSERTS are impacted by a (long) series of factors - few listed below - so is NOT guaranteed that ANY two row inserts take the same amount of time right?
Plus some imports (like SSIS) are done in 10Kbatches and what you do there?

So inserts related "brakes":
PK,Indexes,triggers,disk IO queue, DB auto-growth events, ETC...

And if you don't know how many rows are to be imported anyway...what's the benefit of getting the "average number of inserts per second(or minute)"? Benchmarking? there are tools that can help you do that if needed.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jacobJLAuthor Commented:
Well i have 100s of files that need imported and i am just trying to get an idea of how long they will take based off the data loaded. each file size is different but the data is basically the same. I have an idea of the speed based off how long the file takes from time of import till completion but was thinking this would be more accurate if it was possible. Doesn't sound like it is possible.
0
jacobJLAuthor Commented:
I think i figured out what i need. I just created a temp table and ran the below query a few times. From there i can manually calculate the times and number of rows. Its not pretty but i think it will give me an idea.

select count(*) as count, getdate() as date  into #count  from tablea

insert into #count
select count(*) as count, getdate() as date  from tablea

From there I would think i could use like a scalar variable to find the datediff and difference in row counts, but that seems like too much effort.
0
jacobJLAuthor Commented:
Actually i figured it out
declare @firstcount int= (select count(*) from TableA)

waitfor delay '00:00:59.999'

declare @secondcount int= (select count(*) from TableA)

select @secondcount-@firstcount as NofRecPerMinute
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
I suggest you add the NOLOCK hint like below just because on large volumes the count may block your inserts:

select count(*) from TableA WITH(NOLOCK)
0
jacobJLAuthor Commented:
This is what i needed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.