i need some metircs or benchmark for the length of stored procedure.

Question

hi,

I was asked to optimize a stored procedure, which I noticed

step1) was getting 1 million records from three main tables and 3 masters(small) with 10 cols in a temp table.
step2) then there were fifteen groups on the above temp table and were inserted into a new temp table
step3 then there was the final select query which used the second temp table to show the groped data with fifteen
left joins , to show fifteen cols for each group by.

my question is with this much of data and with a database size of 2.5 gb on local computer with single user, what should be the speed of this query.

i have good index on it, but the question is what should be the base line.

i have leveno 490, icore 7 1.8 gigah, 16 gb ram

yours sincerely

Answer 1

It is very difficult to say what is the "right" performance, as there are so many things involved. And while you have described the scenario, there could be details in the queries that could make me change my mind if I would learn about them.

But my gut feeling is that you could expect an execution time between one and ten seconds.

It may be more relevant to ask from the perspective what the operation is supposed to achieve and what execution time the users are prepared to accept.

Answer 2

Hi @rajesh yadav ,

Indeed, it is difficult to define what execution speed a query should have. Because there are many factors that affect the execution speed, such as the common ones:
No index or index is not used (this is the most common problem of slow query and a defect in program design)
I/O throughput is small, forming a bottleneck effect
The query is not optimized without creating a calculated column
Not enough storage
Slow network
The amount of data queried is too large (multiple queries can be used, other methods to reduce the amount of data)
Lock or deadlock (this is also the most common problem of slow query, it is a defect of program design)
sp_lock, sp_who, active user view, the reason is to read and write resources competition
Unnecessary rows and columns were returned
The query statement is not good and not optimized.

So it is difficult for us to provide you with practical advice. As far as tsql is concerned, we can only provide you with help in optimizing statements.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.