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.
Jacob LepleyBusiness Systems Programming AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
Jacob LepleyBusiness Systems Programming AnalystAuthor 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.
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.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

Jacob LepleyBusiness Systems Programming AnalystAuthor 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.
Jacob LepleyBusiness Systems Programming AnalystAuthor 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.
Jacob LepleyBusiness Systems Programming AnalystAuthor 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

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)
Jacob LepleyBusiness Systems Programming AnalystAuthor Commented:
This is what i needed
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.