dcsimg
Results 1 to 6 of 6

Thread: Database speed

  1. #1

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    771

    Database speed

    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

    Name:  Lastname Properties.JPG
Views: 193
Size:  42.4 KB
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,409

    Re: Database speed

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

  4. #4

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    771

    Re: Database speed

    Hi John,

    I understand. Thank you very much for your explanation!
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  5. #5
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,301

    Re: Database speed

    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.
    Slow as hell.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,692

    Re: Database speed

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width