Database speed

[schoemr]

Hi,

I am trying to see how I can improve on the send/receive speed of my database. Sometimes that server can be slow and I was thinking to optimize where possible. One thing that I was wonder about is this: In the picture below I have a column in a table (one of many, many more) In this example I have LastName.

If I go to dataset designer and look at properties for LastName (in tblUsers) I notice that the MaxLengh property is a very large number. A last name will likely never be more than 150. So my question is, does the database 'reserve' that size and thus may impact on performance? If I go to ALL the columns in ALL the tables and remove that large numbers and set it to appropriate values will it increase performance for send/receive data?

Thanks

Attachment 160819

[jmcilhinney]

This thread really belongs in Database Development rather than VB.NET, so I have asked the mods to move it.

Firstly, it depends on the data type in the database. For instance, if your data type in SQL Server is 'varchar' then a field only contains the number of characters you save. If you specify the data type as, for instance, 'varchar(50)' then that means a maximum of 50 characters but if you save 20 then only 20 are stored. If you use 'char(50)' instead then there will always be 50 characters stored and data shorter than that will be padded with spaces. That's why you should pretty much always use variable-width data types unless you know that your data will always be the same width.

Using a max size in the database doesn't improves performance if the data is smaller but it does prevent stupidly large data being accidentally or maliciously saved that might cause a performance issue.

As for your DataSet, I'm not sure whether it will automatically pick up the column width from the database but it won't affect performance either way. Again, it's more about data validation than anything else. If you have a MaxLength ste on your DataColumn then data added that is too big will be flagged there and then rather than waiting until you save it to the database.

[si_the_geek]

Thread moved to the 'Database Development' forum

[schoemr]

Hi John,

I understand. Thank you very much for your explanation!

[sapator]

If this is moved to the SQL Forum then I suppose we are talking about fixing the data at SQL Server Level.
So what JMC said but link that to the MS SQL Server table development.
Also when we talk about performance speed there are tons and tons of consideration to be take in advice.
What pops in my mind is using indexes to improve performance on select(this is a huge huge chapter), also be sure to select the data you need to be brought to the dataset (not select *).
There are other considerations like making sargable queries or optimize your SQL plans but for a first step would concentrate in indexing the SQL Tables correctly and then move to .NET manipulations.

[szlamany]

Hi Michelle - you are going to need to show us the actual queries that you feel are running slowly.