General information
Title:
SP execution in .NET is very slow
Hello,
Can you give me some advice about the following problem? I have a stored procedure in MS SQL database, which runs instantly in Query Analyzer (about 1 sec in all cases), and takes up to 4 minutes in .NET application, depending on input parameters. All this time is taken by SqlDataAdapter.Fill() method. Here is a piece of SP code, where the problem is localized:
SELECT e.EmplName, e.Office, x.Project, x.ProjectManager, x.Hours
FROM #Employee e
LEFT JOIN (
SELECT Resource COLLATE Cyrillic_General_CI_AS AS Resource, Project COLLATE Cyrillic_General_CI_AS AS Project, pm.Name AS ProjectManager, Hours
FROM #ResourceTimesheet
LEFT JOIN PROJTABLE p ON p.NAME = Project COLLATE Cyrillic_General_CI_AS
LEFT JOIN EMPLTABLE pm ON pm.RECID = p.Responsible
) ...
I found that execution time under .NET depends on number of records in #ResourceTimesheet table. Each record additionally takes approx 1 sec. With 250 records the execution time is about 240 seconds. The problem disappears with removing of both JOINS in the inner query. Changing the 'LEFT JOIN PROJTABLE' to inner join also significanly reduces execution time (approx. 8 times), although all records in #ResourceTimesheet have matching records in PROJTABLE.
I cannot understand such significant difference between execution in QA and SqlDataAdapter. Am I doing something wrong or this is known issue?
Thanks in advance,
Alexander
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only