Optimising for performance against large SQL Server databases

selwonk · Post by **selwonk** » Fri Nov 17, 2023 12:44 am

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 · Post by **MichaelG** » Fri Nov 17, 2023 8:26 am

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 · Post by **selwonk** » Sat Nov 18, 2023 4:52 am

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