Link to home
Start Free TrialLog in
Avatar of Jimbo99999
Jimbo99999Flag for United States of America

asked on

SQL Query - Running "Sluggish" from VB.Net

Good Day Experts!

I have had help with converting this existing VB6 code to VB.Net and am trying to test now.
The problem is that it is running very sluggish due to the amount records in the table that the Query is running against.  The table has 18million+ records.  

The Primary Keys are already set on the table.  Can applying foreign keys or constraints on the table make a performance difference?

Thanks,
jimbo99999
Avatar of jziegler55
jziegler55

It depends.  See link below published on EE.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_4293-Can-Foreign-key-improve-performance.html

Are your queries all running on server side?
SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I hope you are not retrieving the 18M records to display in a grid? What is slow exactly?
Avatar of Jimbo99999

ASKER

Good Day Everyone

I do not have to display the records in a grid.  The table has 18M+ records and I have to run 2 queries at the moment against the table.  The results are 675records and 1415records.  
The "sluggishness" is in the processing time for the queries. They avg about 17minutes.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for responding.  I reviewed the queries I am using to make sure they are formed correctly in the where clause for proper grouping.  After further investigation, I found the server that the db is on has very high traffic during the times that I was trying to query the table.  After peak hours, execution decreased the processing time substantially.

I also restructured the flow of the program code a bit to increase performance.  

Looks like the report will have to be scheduled to run after hours.
Good to hear. At least you know that it was your connection and nothing you had wrong in your application.
Thanks all for responding.

While I "kind of" came to a conclusion after investigating here, I did start poking around based on your responses.  Also,  your repsonses provided knowledge transfer as I am still learning.

jimbo99999
I hope you like Learning, because you will still be Learning in many years from now.

I have been working with VB since 1993, and with .NET since 2000. I still learn new stuff everyday.

(Sorry for the uppercase on Learning, damn autocorrector on Windows 8 keeps adding them because it is set for French, and the French corrector is awful. Learning is not even a word in French, and instead of flagging it as bad, it corrects it automatically with the uppercase).