October 1, 2015 at 4:56 am
Hi,
At our company we have a program that is fetching data from our SQL-server. There is not so much data in the different tables (100.000) in the largest tables...
However, when fetching the data it is done by populating UserDefined table types and have these as in-parameter to a "head"-SP. In this "head"-procedure many calls are made to "2nd"-level procedures. And is then returned. It can be as many as 60-70 calls to these "2nd"-level procedures. And now the Customer is complaining aobut bad performance. I was thinking if there is any other way to do this.
The database has Clusterd indexes on GUID-columns (uniqueIdentifiers) that is as well the primary key, which can be the culpit here. One thought could be to insert an integer-column that is automatically incremented and continue having the primary- & Foreign-key on the GUID-columns.
The GUI is a web-based program developped in C#.
My key-question is if there is another way round these calls to the 2nd-leel queries. Maybe having some other result-tables?
//Micke
October 1, 2015 at 10:29 am
No way to answer such a general question. Procedures calling procedures however doesn't cause bad performance. Badly written code and poor indexing causes poor performance. You either need to do a general tuning exercise on your database, starting with identifying what *is* causing the problems, or you need to get a 3rd party in to do one for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2015 at 9:04 am
You can find the longest running couple of procs in sys.dm_exec_procedure_stats, order by 1.0*total_elapsed_time/execution_count desc.
Have a look at the query plan.
i.e., find a test case, execute it with View actual plan.
If you can then give us the actual executed plan ,I am sure there are massive amounts of SQL guru's who will gladly help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply