MSSQL Query takes 10 times longer to run when called from a C# program

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 406 Views - Last Post: 30 September 2019 - 05:22 AM

#1 jcborland   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 137
  • Joined: 16-December 13

MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 03:59 AM

Hi,
I have a parameterised SQL query which takes about 2 minutes to run when I run it natively from within SQL Server Management Studio. However when I call the same SQL query from a C# program it times out after 30 minutes.

Does anyone have any idea why this would be the case?

I've included the C# code that calls the SQL Query.

Thanks,
Jim.
sqlDataConnection.Open();
SqlCommand sqlCommand = new SqlCommand(sqlSelectStarToUnity, sqlDataConnection);
//
// Add parameters here
//
sqlCommand.Parameters.AddWithValue("DatabaseID", clientRef);
sqlCommand.Parameters.AddWithValue("Latest", "False");
sqlCommand.Parameters.AddWithValue("StartDate", strStartDate);
sqlCommand.Parameters.AddWithValue("EndDate", strEndDate);
sqlCommand.Parameters.AddWithValue("Frequency", frequency);
sqlCommand.Parameters.AddWithValue("RunDate", strRunDate);
sqlCommand.Parameters.AddWithValue("PYear", pYear);
sqlCommand.CommandTimeout = 1800;

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(payHistoryDataSet, queryName);



Is This A Good Question/Topic? 0
  • +

Replies To: MSSQL Query takes 10 times longer to run when called from a C# program

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7501
  • View blog
  • Posts: 15,544
  • Joined: 16-October 07

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 04:20 AM

You're saying Management Studio completes the query in 2 minutes? Meaning the tool says it's done? Of course, the tool is smart enough to not have loaded all that data into memory yet...

You code does try to load the entire result set into memory. And that may be the core of the problem.

If the queries are identical, then SQL Server will handle them identically. However, how you, the client, deal with that result set has a number of variations. Because of this, I'm assuming you have a lot of rows. If this is not the case, then the queries probably aren't identical in some way.

To compare apples to apples, you could write a stored procedure and run it both in SSMS and your code.
Was This Post Helpful? 1
  • +
  • -

#3 jcborland   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 137
  • Joined: 16-December 13

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 05:27 AM

Hi baavgai,

I have 64,299 rows does that count as a lot of rows?

I think the server is struggling, memory usage is sitting at around 90% so that might explain why it's struggling load the entire result set into memory.

If the code was using a stored procedure would that make a difference? I don't have any control over whether stored procedures are used or not but it would be good to know if it would help.

Thanks,
Jim.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3000
  • View blog
  • Posts: 11,549
  • Joined: 03-December 12

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 05:48 AM

I don't see the query itself. Do you NEED 60,000 rows at one time, or can you paginate them to improve performance?
Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7501
  • View blog
  • Posts: 15,544
  • Joined: 16-October 07

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 07:08 AM

View Postjcborland, on 27 September 2019 - 07:27 AM, said:

I have 64,299 rows does that count as a lot of rows?

It really depends on the row. If it's just a couple of strings, then not really. If it's a dozen hefty columns, then probably. ( 64K is kind of a magic RAM tipping point. )

View Postjcborland, on 27 September 2019 - 07:27 AM, said:

I think the server is struggling, memory usage is sitting at around 90%

SQL Server, left to it's own devices, will slurp up as much memory as it can. You can tweak that in settings. The struggle would be CPU usage. Two minutes for so few rows on the SSMS side is quite long. You might want to check index hits for that.

View Postjcborland, on 27 September 2019 - 07:27 AM, said:

If the code was using a stored procedure would that make a difference?

Well, it's really about consistency across contexts: exec foo 'bar' should behave the same no matter where you are. It can help to the extent that you can dictate and tune the SQL used for a given pull and enforce that in the app. However, no, a stored procedure and a parameterized SQL statement should behave the same.
Was This Post Helpful? 0
  • +
  • -

#6 jcborland   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 137
  • Joined: 16-December 13

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 07:51 AM

Thanks for your help. I'm going reboot the server this weekend and see it that helps. I'll let you know if it does.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15360
  • View blog
  • Posts: 61,601
  • Joined: 12-June 08

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 07:52 AM

REbuild those indexes if you haven't in a while.
Was This Post Helpful? 1
  • +
  • -

#8 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 11:01 AM

We haven't seen the query yet, or a representative version of it. It could be very poorly formed.

64k, though, is not typically a huge number.

Triggers, indexes, other "stuff" going on would be my focus initially.
Was This Post Helpful? 1
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 27 September 2019 - 11:10 AM

Adjusting a query to return 500, 1000, 5000, etc., rows can also be useful to give clues.
Was This Post Helpful? 0
  • +
  • -

#10 jcborland   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 137
  • Joined: 16-December 13

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 29 September 2019 - 05:04 AM

Hi,
Just to update you all. I haven't resolved this yet but I'm thinking there may be some corruption of the data!

I'm not concerned about the query taking 2 minutes when run within Management Studio (MS) what I'm concerned about is the query not finishing at all when it's called from a C# program. I set the timeout to zero (infinity) and left it for over 3 hours and it never finished.

The query itself is over 2,000 lines of SQL code. It starts by dropping and recreating several tables and inserting data into the newly created tables. It then continues to run 25 SELECT statements joined together using UNIONs. I have found that if I comment out 2 of the 25 SELECT statements the query runs fine from C#.

Here are some stats :

The whole query.
MS : returns 64,299 rows in 1:52 (1 min : 52 secs)
C# : never finishes even after over 3 hours

The query with 2 SELECTS commented out.
MS : returns 62,455 rows in 1:14
C# : returns 62,455 rows in 2:18

The 2 SELECTS mentioned above on their own.
MS : returns 1,844 rows in 1:04
C# : returns 1,844 rows in 11:52

The 1st of the SELECTS mentioned above.
MS : returns 58,672 rows in 0:27
C# : returns 58,672 rows in 7:37

The 2nd of the SELECTS mentioned above.
MS : returns 58,672 rows in 0:26
C# : returns 58,672 rows in 8:00

Baavgai you asked if the queries were identical and the answer to that is yes as far as they can be. The only difference is that when I run it in MS I have to declare and set the parameters at the top of the query, when I run it from C# I have to comment those out. Otherwise I'm calling the same query, it's called YearToDates.sql.

I tried cutting and pasting a bit of the SQL Query into this but the site blocked me and I had to re-log in and start this reply all over again.

I try to send some in another reply.

Regards,
Jim.
Was This Post Helpful? 0
  • +
  • -

#11 jcborland   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 137
  • Joined: 16-December 13

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 29 September 2019 - 05:18 AM

Hi,
I've tried to attached the SQL query to this, it doesn't always work for me.

This query is being used on over 2,000 databases without any issues. The problem is occurring on a fairly large one but there are larger ones working fine.

For example on one of the databases a record set containing over 109,000 rows is returned no problem.

Thanks,
Jim.

It keeps saying "Upload Skipped (No file was selected for upload)"!
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15360
  • View blog
  • Posts: 61,601
  • Joined: 12-June 08

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 29 September 2019 - 10:14 AM

Perhaps use something like http://sqlfiddle.com or https://pastebin.com/
Was This Post Helpful? 0
  • +
  • -

#13 jcborland   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 137
  • Joined: 16-December 13

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 29 September 2019 - 10:29 AM

Hi All,
I've got all sorted, thank you all for your suggestions.

I ended up rebuilding all the indexes for all the tables involved in the query.

The odd thing is that the time taken to run the query, in MS, went up from around 2 minutes to over 8 minutes but then running it from the C# program started to work taking about 10 minutes but finishing which was the main thing.

I've since had a look through the SQL and found a few unnecessary cross joins and it's running in just over 1 minutes in MS and C#.

I'm going speak to the author of it and get her to go through it with a fine toothed comb and take any unnecessary elements.

Thanks again,

Jim.
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15360
  • View blog
  • Posts: 61,601
  • Joined: 12-June 08

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 29 September 2019 - 10:34 AM

Quote

I'm going speak to the author of it and get her to go through it with

You aren't the author? Ahh.. weird.


Quote

It starts by dropping and recreating several tables and inserting data into the newly created tables.

You should ask the original author why they are not using 'trunct' instead of dropping tables. Typically a better option.

------
Moving to MSSQL.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: MSSQL Query takes 10 times longer to run when called from a C# program

Posted 29 September 2019 - 11:42 PM

If you create an execution plan in Management Studio I would have that it would give clues to bottlenecks.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2