Optimising for performance against large SQL Server databases

This public forum is for user-to-user discussions of ASP.NET Maker. Note that this is not support forum.
Post Reply
selwonk
User
Posts: 5

Optimising for performance against large SQL Server databases

Post by selwonk »

My SQL Server database is 0.8TB and I am running into some performance issues. In particular, I have some very large tables containing big chunks of XML and JSON data.

When viewing data in these tables, I keep getting timeouts. The main problem seems to be that when selecting a single record for some reason it always loads the entire table. From the debug:

0.015: SELECT * FROM [dbo].[API_quote_requests]

0.031: SELECT COUNT(*) FROM [dbo].[API_quote_requests]

Please, can anyone help me to understand why it is doing a "SELECT * FROM" and how I can fix this?

Also, best practise would be to do the following to avoid transaction locks:

SELECT COUNT(*) FROM [Table] WITH (NOLOCK)

...and it would be great to understand if I can meddle with the templates for SQL queries in some way.

Thanks in advance.


MichaelG
User
Posts: 1139

Post by MichaelG »

To select specific fields only, you can add the following codes in the Table_Load server event:

SqlSelect = "SELECT field1, field2, etc... FROM table1";

selwonk
User
Posts: 5

Post by selwonk »

Thank you, Michael. I will have a look at that.


Post Reply