Page 1 of 2 12 LastLast
Results 1 to 40 of 42

Thread: how to use a variable from my DB into my VB.net code

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2016
    Posts
    60

    how to use a variable from my DB into my VB.net code

    Hi guys just a question? i would like to know how to use the variable 'STATUS' column in my DB into my application. In my application i have an Authentification Menu where i can check the STATUS if the user is connected or not. and get a message.
    There is the table in my DB called Authentification with the columns below:
    Username,Profil,Password and STATUS.

    Below is the Button where i can check the STATUS

    Code:
    Dim STATUS As String = ""
    Dim MyConnexion As SqlConnection = New SqlConnection("Data Source=10.1.1.221,49170;Database=Gestock1;Uid=sa;Pwd=Admin123")
            Try
                ' MyConnexion = New SqlConnection("Data Source=OKOUELE\SQLEXPRESS;Initial Catalog=Gestock;Integrated Security=True")
                MyConnexion = New SqlConnection("Data Source=10.1.1.221,49170;Database=Gestock1;Uid=sa;Pwd=xxxxxxx")
                If MyConnexion.State <> ConnectionState.Open Then
                    ' MsgBox("Connecté à la base de données SQL")
                    MyConnexion.Open()
                Else
                    MsgBox("Echec de connection!!!")
                End If
    
            Catch ex As DataException
                MessageBox.Show(ex.Message)
            End Try
    
    TheSqll = " select * from Utilisateurs where Profil = '" & ComboType.Text & "'and Username = '" & TextUsers.Text & "' and Password ='" & TextPass.Text & "'"
    
            If STATUS = "Connecté" Then
    
                MsgBox(" Utilisateur '" & TextUsers.Text & "' déjà connecté ")
    
            ElseIf STATUS = "Déconnecté" Then
    
                MsgBox(" Utilisateur '" & TextUsers.Text & "' non connecté ")
            End If
    How do i use this variable from my DB into my code???

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: how to use a variable from my DB into my VB.net code

    1) Don't ever use select * ... write out the names of the fields you're selecting.
    2) You're not actually trying to get the value of a variable from SQL, you want a value from a field in a table, selecting that is no different from selecting any other field - I suggest looking at the .ExecuteScalar method in conjunction with the previous recommendation of naming fields.
    3) Parameters. Please, please, for all things SQL, use parameters. Don't be a victim of Little Bobby Drop Tables

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    1) Don't ever use select * ... write out the names of the fields you're selecting.
    tg, why not use *? Just curious

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    tg, why not use *? Just curious
    Because that is taking up unnecessary bandwidth. Why bring back everything when you just use what is needed? It may seem trivial but do that with 10,000+ users and it can be a drag on performance. I can also poorly affect unrelated systems needing bandwidth.
    Please remember next time...elections matter!

  5. #5
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by TysonLPrice View Post
    Because that is taking up unnecessary bandwidth. Why bring back everything when you just use what is needed? It may seem trivial but do that with 10,000+ users and it can be a drag on performance. I can also poorly affect unrelated systems needing bandwidth.
    I guess I am kind of looking for more of a technical reason. Not saying that you're wrong, but to say to NEVER use * seems kind of a matter of opinion than a best practice, but I dont know, maybe he knows something I do not... Are you suggesting that even if I need all of the columns I should manually type them all out in my statement? It does seem trivial.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: how to use a variable from my DB into my VB.net code

    There's a couple reasons. Bandwidth is one. IF all I need is one field, why pull over the other 3 or 4? Today it maybe 3 or 4 and it probably doesn't matter much. Then one day someone decides, hey, let's add employee photos... now not only are you pulling those extra 3-4 fields, you're pulling down the binary data for a photo. There's no need for that. Plus it just creates noise with information overload. Also, with things like SQL Server, it leads to cleaner execution plans. Another issues I've seen with select * is when fields are referenced ordinally rather than by name... and then a field gets moved because the table was altered... now it's no longer in the same spot. OR, when the query has to be updated and a new table is joined in... if the tables happen to have columns with the same name, you'll have to alias at least one of them.
    I know it seems trivial, but trust me, in the long run, at the end of the day, it does end up saving time an heartache.
    The only time I use Select * is when I'm initially building a query - just to get the fields... and then I'll quickly start replacing it with jsut the fields that I need. I rarely ever need every single field in a table. When I do, it's only a few fields in the table anyways, or if there's a lot - did you know in most DBMSs you can right click a table and generate a select that will create a select statement with all of the fields for you.

    And yes, it is a best practice. http://www.dpriver.com/blog/2011/09/...est-practices/ This isn't the only list where I've seen this... here's another https://dzone.com/articles/20-database-design-best ... and here http://ascherconsulting.com/archive/...in/a/sql/query ... and well.... google "avoid using select * in sql" for more...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    I can certainly understand not wanting BLOB data if youre not going to use it. In the same respect that you mentioned, if a column is removed from the table then it's going to throw an exception. Obviously not something that is going to happen. I will read through the links you provided. May even go as far as running some tests. Im not arguing, and I quite often only use a few column names also but I am not so cautious to avoid using * altogether.

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    I guess I am kind of looking for more of a technical reason. Not saying that you're wrong, but to say to NEVER use * seems kind of a matter of opinion than a best practice, but I dont know, maybe he knows something I do not... Are you suggesting that even if I need all of the columns I should manually type them all out in my statement? It does seem trivial.
    It is also part of overall programming efficiency techniques. Don't make the program less efficient when you can help it. Think of a system with hundreds of programs and users all working at the same time. Every little inefficiency adds up and takes away from the system. You could call "NEVER use *" an opinion. It is almost universally applied by professional programmers.
    Last edited by TysonLPrice; Jan 29th, 2018 at 11:32 AM.
    Please remember next time...elections matter!

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: how to use a variable from my DB into my VB.net code

    Additionally, according to some SQL optimization, SELECT * performs worse than writing out all the column names even if you WANT all the columns. Of course, it's a bit tedious to write out all the columns, so I would say that the rule should be somewhat modified. If you want the program to perform as best it can, then don't use SELECT *, but there are certainly programs where we don't care whether they perform well. For example, lots of little test applications, created to test something then discarded, have no reason not to use SELECT * because nobody really cares how they do.

    But back to the original code. You create a connection, then you create a connection, then you test whether the connection is open, and if it is not then you open it. That sequence of steps makes no sense. How could the connection be open when you just created it? Why create it twice?

    A better design would be to make use of the Using construct:
    Code:
    Using cn As New Connection(your connection string here)
     Using cmd As Command = cn.CreateCommand
       Try
         cn.Open
        'The work goes here.
        'See Following Discussion
       Catch ex As Exception 
         'Do something, even if only logging.
       End Try
     End Using
    End Using
    The advantage of the Using construct is that it cleans up the objects regardless of whether an exception is thrown. The Using blocks could be inside the Try block and would still clean up the connection if it throws an exception. Of course, in that case, you wouldn't be able to do anything with the connection or the DB in the catch block, so you won't be handling anything unless the Using block is outside the try block, as I showed.

    As to what you would be doing, it looks like you only want one value from the row. In that case, you'd set the cmd.CommandText to the SQL you wanted to execute, and you'd REALLY not want to use SELECT * for this, because you have an excellent opportunity here. There are various ways you can get data back from a database, and which you use depends on what you want. If you only want a single value, you can call:

    Dim yourValue = cmd.ExecuteScalar

    This is the fastest thing you can do, but it returns ONLY one value. In fact, it returns the value from the first field of the first row. So, if you use SELECT *, you STILL get just the first value of any row, so you really need to write the column that you need the data from.

    Another point is that it returns an Object. This may be Nothing or DBNull if the SQL didn't return anything. So, check what 'yourValue' holds, and if it is not Nothing, then convert it to the type that it should be.

    If you actually need more than one value, then you'd need a DataReader or Datatable. The DataReader is forward only and is faster than a datatable, but a datatable is more versatile at the cost of slightly slower speed. In this case, it looks like you only want the one value, so ExecuteScalar should do.
    My usual boring signature: Nothing

  10. #10
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    It does not appear to me that there is a sufficient enough case study data to justify never using the wildcard. I guess it would be a good reason to explicitly use column names if you have a concern a column could be added that wasn't expected at buildtime. You may think I am arguing, but all I am seeing is a few articles stating that the entire dataset needs to be copied in order to give you all the column names, which leaves much to the imagination, and some other claims it reduces I/O on your disk.

  11. #11
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: how to use a variable from my DB into my VB.net code

    I'd say that whether or not you use a wildcard isn't really part of the question the OP asked, so it should be taken up in a different thread specific to the topic.
    My usual boring signature: Nothing

  12. #12
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    agreed

  13. #13
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    agreed
    I disagree...topics often go a little off subject when posters disagree with what is being said. Particularly when disputing a programming technique grounded in decades of database programming. kpmc stated "It does not appear to me that there is a sufficient enough case study data to justify never using the wildcard" but I wonder how much research went into that statement. I suspect very little because it is a poor\lazy coding technique. It is also is wasteful in I/O, since you will be reading all of that data off of the pages, when perhaps you only needed to read the data from the index pages. It is also wasteful in network traffic and in many cases the memory required by the consuming application to hold the results. This becomes even more of an issue when joins are involved.

    I just wanted to make this point in case someone else reads these posts and thinks the old saw of not using "select *" is just some trivial thought. Thinking along the lines of "how can that one line really matter" is indicative of missing the big picture of large system platforms where every efficiency is important to the entire system. Sure it probably won't adversely affect a home made appointment tracker. It could definitely affect large billing systems for a corporation.
    Last edited by TysonLPrice; Jan 30th, 2018 at 06:43 AM.
    Please remember next time...elections matter!

  14. #14
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    Lazy, maybe, poor? i see no reason to believe it is poor coding practice... All you have shown me is your assumption, again. I've googled it and there really is not that much out there. 4 maybe 5 articles, I dont recall. I spent maybe 15-20 minutes looking for some kind of official document, or some kind of experiments with results, and all I found was "Just dont do it, ok!"

  15. #15
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    Lazy, maybe, poor? i see no reason to believe it is poor coding practice... All you have shown me is your assumption, again. I've googled it and there really is not that much out there. 4 maybe 5 articles, I dont recall. I spent maybe 15-20 minutes looking for some kind of official document, or some kind of experiments with results, and all I found was "Just dont do it, ok!"
    OK...Believe what you want regardless of established facts and understanding. You seem have this gut feeling about something you prefer to believe versus what really happens. I'd suggest you ask some senior programmers you trust.
    Please remember next time...elections matter!

  16. #16
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: how to use a variable from my DB into my VB.net code

    That's your perogative. All I can do is give the advice and experience, it's up to you to decide if you want to follow it or not. I also think you should always use table aliases, always use those aliases with fields, never use on error resume next, never use DoEvents, and always tell the people you love that you love them. But I ain't going to force it either. All I know is that I've been doing this most of my life and long enough that I've seen the difference these little things make. I also come from a time when performance mattered not just in CPU processing and squeezing every thing out of it you can, but also when LANs weren't the high speed demons they are today. First LAN I worked on was a Token-Ring coax cable ethernet where everyone was tied in serial. One day one of our taller developers stretched out, knocked one side of his t-connector off... one by one, each of us in the office started dropping off the network as the token passed us by.
    So, if you looking for some paper with the Microsoft logo stamped on it with a "Thou shalt not use select splat in thine SQL statements..." you're not... what you're going to find is people speaking from experience, those of us that have, over time, noticed these little things, because we've HAD to pay attention to these kinds of things in the past.
    Also... take a look at some execution plans in SQL server sometime as well. Including fields needlessly can sometimes trigger scans that do take time, again, it may be a little bit, but a little bit here, a little bit there, across multiple users, can add up. And it may not be a problem on the client, but it can be a problem on the server over time. The application I work on can be hosted. In a shared environment. Which means on any given server, we can have a couple hundred clients. Each of those clients can easily have anywhere from a dozen to hundreds of users at a time. Not only that, but they all also have various processes running at the same time, some one-off, some scheduled. That's a piss-load of SQL all going on at the same time. That's a lot of data being moved around. So I have to be conscientious about this and only pulling the data need, no more. No less. Five is right out. You may discount it, but I have been in technical interviews where I've been asked point blank about query optimizations where this is one of those techniques that's expected.
    It's also about data security. In among some of the data I deal with is sensitive data. Personally Identifiable Data (PID) - crap I have to get trained, retrained, and recertified every year - so I have to be careful about what data is transmitted to minimize potential threats.
    And then there's maintenance. Six months from now, you need to add a field, or modify how an existing field is calculated, or remove a field, rename it, or something... it's going to be far far easier to do that if the fields are already listed out. Just yesterday, I had to swap a field for another. Easy. I go to the line where hte old field is, I tap -- to comment it out, press, END then ENTER, and type in the new field, add a comma, save, load the query and I'm done. Took me longer to find the query in TFS than it did to make the change.
    Lastly there's your safety and the sanity of your co-workers to think about. They won't go nutter trying to figure out why a column suddenly moved in a grid and you won't get strung up by your toe nails.

    But at the end of the day, it's your code, you have to live and die by it. But I'm also reasonably sure that it won't pass any code review in any professional shop out there. I know it would never pass in any of the shops I've ever worked in.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  17. #17
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    I've been doing this a while also, since Novell 4.11 and coax networks, though I was real young in those days and was not in any sort of production environment. My production experience started in 1995ish with Lotus and LotusScript, which essentially is VBScript, and used ADODB.

    You guys appear to think I am arguing, or not trusting your advice, etc... When I am the type that like proof, I am this way by nature on all things.

    Anyway, I actually just added a column to a table, and I am actually very happy that I do not have to open that solution, add the column, rebuild, and redeploy. It is not for a multi billion transaction per second table and all is well, and sure...lazy, I get it.

    I've got some experiment ideas how I am going to see some real results using wildcard vs not.

    btw, I love you too <3 God Bless.

  18. #18
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    I've been doing this a while also, since Novell 4.11 and coax networks, though I was real young in those days and was not in any sort of production environment. My production experience started in 1995ish with Lotus and LotusScript, which essentially is VBScript, and used ADODB.

    You guys appear to think I am arguing, or not trusting your advice, etc... When I am the type that like proof, I am this way by nature on all things.

    Anyway, I actually just added a column to a table, and I am actually very happy that I do not have to open that solution, add the column, rebuild, and redeploy. It is not for a multi billion transaction per second table and all is well, and sure...lazy, I get it.

    I've got some experiment ideas how I am going to see some real results using wildcard vs not.

    btw, I love you too <3 God Bless.
    I'm confused...if you don't need to open that solution, add the column, rebuild, and redeploy; what does the program have to do with the new column you added?
    Please remember next time...elections matter!

  19. #19
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    The column is available in the app because i used a wildcard

  20. #20
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    The column is available in the app because i used a wildcard
    What does the program have to do with the new column you added? If it was actually needed how could the program not be changed. If it isn't used, you just proved our points by adding unnecessary overhead to it.
    Please remember next time...elections matter!

  21. #21
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    What does the program have to do with the new column you added? If it was actually needed how could the program not be changed. If it isn't used, you just proved our points by adding unnecessary overhead to it.
    Im confused how youre confused?

    the column was a bit and is to be used to determine if an employee is a manager or not. A new requirement from HR. Now they have new check box's

  22. #22
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    Im confused how youre confused?

    the column was a bit and is to be used to determine if an employee is a manager or not. A new requirement from HR. Now they have new check box's
    You said you added a new column, seem to imply the program is using it, has a new checkbox, but you didn't change the program? That is what is confusing me.
    Please remember next time...elections matter!

  23. #23
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    Right, I added a column VIA SQL Server Manager Tool. The app is aware of the new col without me having to go into the app and manually add it to the command, instead it is using "SELECT *" thus the column is available in the DGV

  24. #24
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    Right, I added a column VIA SQL Server Manager Tool. The app is aware of the new col without me having to go into the app and manually add it to the command, instead it is using "SELECT *" thus the column is available in the DGV
    But doing absolutely nothing; all you did was add overhead as far as I see. If you actually used it you would "have to open that solution, add the column, rebuild, and redeploy"...I think you are being deliberately obtuse and I'm outta here.
    Please remember next time...elections matter!

  25. #25
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    If you actually used it you would "have to open that solution, add the column, rebuild, and redeploy"
    What am I missing here? Why would I need to do that, what would I need to change?

    I'm outta here.
    Peace!

  26. #26
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: how to use a variable from my DB into my VB.net code

    I think I can find you a source, if you'd really like to see one. I'm sure I've got one somewhere that describes the extra work that SQL Server has to do to figure out what * means. This makes it slightly less efficient, but that's just time. Most programs spend almost all their time doing nothing, so wasting a few milliseconds makes no difference. LOADS of things we do are not the most efficient possible code, but have other advantages that outweigh the speed difference....which you won't see except in specific (quite rare) situations.

    So, SELECT * is not the fastest possible, but whether that matters depends on the situation. One could say the same of LINQ, loops, and many other things.

    In any case, the OP has not come back, so it's not clear that we derailed anything, but the efficiency of SELECT * seemed quite secondary to the actual problem.
    My usual boring signature: Nothing

  27. #27
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    I apologize, looking back I should have kept my mouth shut. To never use wildcard seems to be good advice if you're trying to pass an efficiency exam, or if you are writing queries for amazon.com shoppers, but otherwise...

    I would appreciate if you did find me a source that is more technically based

  28. #28
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: how to use a variable from my DB into my VB.net code

    Well, nevermind. I took a brief look, then decided I'd try a straight up test. The test code was this:

    Code:
        Private Sub test1()
            Dim x As Integer
            Dim n As Long = 1
            Dim d As Double = 117.146
            Dim st1 As String = String.Empty
            
            cmd.CommandText = "SELECT * FROM Session"
            Dim dr As SqlClient.SqlDataReader
    
            For x = 0 To 10000
                dr = cmd.ExecuteReader
                dr.Read()
                dr.Close()
            Next
        End Sub
    
        Private Sub test2()
            Dim x As Integer
            Dim n As Long = 1
            Dim d As Double = 117.146
            Dim st1 As String = String.Empty
            
            cmd.CommandText = "SELECT [SessionID],[Name],[FileName],[LegacyFileName],[FolderID],[Created],[Modified],[Submitted],[SubmitResult],[SubmitMessage],[SubmitKey],[SessionMessage],[MRRProject],[SessionNote],[ProfileID],[RepeatingValueID],[DataEntryLayoutID],[SPDV1],[SPDV2],[SPDV3],[SPDV4],[SPDV5],[SPDV6],[SPDV7],[SPDV8],[SPDV9],[SPDV10],[RecordViewLayout] FROM Session"
            Dim dr As SqlClient.SqlDataReader
    
            For x = 0 To 10000
                dr = cmd.ExecuteReader
                dr.Read()
                dr.Close()
            Next
        End Sub
    It was a test of bringing back a goodly number of records from a table with a good number of fields. There were no WHERE clauses, so the only difference would be the * versus fields written out. I used a datareader, and read the first line, so that the data was accessed.

    There was no difference in performance at all between these tests. Test1 would beat Test2 on some runs, then Test2 would beat Test1 on the next run, and by similar amounts. Each run consisted of each test method being called twice, and I ran this several times. It was clear that the variability between Test1 and Test2 was no greater than the variability between different runs of each test.

    Therefore, there was no difference between * and fields written out in this test.

    I then changed it so that I was comparing SELECT * against SELECT SessionID. In other words, get ALL fields versus getting just one field. In that test, as expected, getting all the fields took about three times as long as getting one. So, moving very little data is faster than moving a lot of data....no surprise there.

    Finally, I switched to filling a datatable using the same SELECT statement, which is just a change from Datareader to Datatable. Once again, there was no difference between SELECT * and writing out all the fields.

    Therefore, what I said was incorrect. I had read that SQL Server has to do more work to resolve SELECT *, but there is no measurable performance impact...but ONLY if you need ALL the fields. There is a dramatic difference if you are bringing back more data than you need.
    My usual boring signature: Nothing

  29. #29
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    I appreciate your time, now you are speaking my language! results!

    I had something similar in mind, however with no data. I am figuring on creating the max amount of columns allowable in a select, which is 4096 in MSSQL of varying types, then some kind of loop which includes stopwatch and avg's. Also watching the memory usage and disk I/O.

  30. #30
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    which is 4096 in MSSQL
    I should say ''up to 4096" as I have never attempted such craziness. Itll be fun regardless.

  31. #31
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: how to use a variable from my DB into my VB.net code

    I just have a test project. You can see that there are variables in each method that aren't used. Those are leftovers from other tests. For every run I call Test1, Test2, Test2, Test1, and use a stopwatch to time each one.
    My usual boring signature: Nothing

  32. #32
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by Shaggy Hiker View Post
    Well, nevermind. I took a brief look, then decided I'd try a straight up test. The test code was this:

    Code:
        Private Sub test1()
            Dim x As Integer
            Dim n As Long = 1
            Dim d As Double = 117.146
            Dim st1 As String = String.Empty
            
            cmd.CommandText = "SELECT * FROM Session"
            Dim dr As SqlClient.SqlDataReader
    
            For x = 0 To 10000
                dr = cmd.ExecuteReader
                dr.Read()
                dr.Close()
            Next
        End Sub
    
        Private Sub test2()
            Dim x As Integer
            Dim n As Long = 1
            Dim d As Double = 117.146
            Dim st1 As String = String.Empty
            
            cmd.CommandText = "SELECT [SessionID],[Name],[FileName],[LegacyFileName],[FolderID],[Created],[Modified],[Submitted],[SubmitResult],[SubmitMessage],[SubmitKey],[SessionMessage],[MRRProject],[SessionNote],[ProfileID],[RepeatingValueID],[DataEntryLayoutID],[SPDV1],[SPDV2],[SPDV3],[SPDV4],[SPDV5],[SPDV6],[SPDV7],[SPDV8],[SPDV9],[SPDV10],[RecordViewLayout] FROM Session"
            Dim dr As SqlClient.SqlDataReader
    
            For x = 0 To 10000
                dr = cmd.ExecuteReader
                dr.Read()
                dr.Close()
            Next
        End Sub
    It was a test of bringing back a goodly number of records from a table with a good number of fields. There were no WHERE clauses, so the only difference would be the * versus fields written out. I used a datareader, and read the first line, so that the data was accessed.

    There was no difference in performance at all between these tests. Test1 would beat Test2 on some runs, then Test2 would beat Test1 on the next run, and by similar amounts. Each run consisted of each test method being called twice, and I ran this several times. It was clear that the variability between Test1 and Test2 was no greater than the variability between different runs of each test.

    Therefore, there was no difference between * and fields written out in this test.

    I then changed it so that I was comparing SELECT * against SELECT SessionID. In other words, get ALL fields versus getting just one field. In that test, as expected, getting all the fields took about three times as long as getting one. So, moving very little data is faster than moving a lot of data....no surprise there.

    Finally, I switched to filling a datatable using the same SELECT statement, which is just a change from Datareader to Datatable. Once again, there was no difference between SELECT * and writing out all the fields.

    Therefore, what I said was incorrect. I had read that SQL Server has to do more work to resolve SELECT *, but there is no measurable performance impact...but ONLY if you need ALL the fields. There is a dramatic difference if you are bringing back more data than you need.
    Something I didn't see you mention though is the overall impact to whole IT systems. For example, the more data is read from the tables, the slower the query will become. It increases the time it takes for the disk operations. Also when the database server is separate from the web server, you will have longer network delays due to the data having to be transferred between the servers. Just looking to see if your program is affected doesn't consider the big picture. Most programs don't run in a vacuum in the corporate world. But I guess that is only worth considering "if you are writing queries for amazon.com shoppers, but otherwise..."
    Last edited by TysonLPrice; Jan 31st, 2018 at 06:30 AM.
    Please remember next time...elections matter!

  33. #33
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    Hey Tyson, I am starting to think you need a tissue, or a hug, or maybe both?

    Maybe in your infinite wisdom you could enlighten us to a better way to test your claims?

  34. #34
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: how to use a variable from my DB into my VB.net code

    My point was just that, if you are returning ALL the columns from a table, SELECT * has the same performance as writing out the fields. I did not think that was true, but my test showed that there isn't any practical difference. The test ALSO showed that getting all the columns back when you don't need them has a VERY significant, totally negative, cost.

    Get just what you need, neither more, nor less. If performance matters, get only those columns that are needed. If performance doesn't matter, do whatever you want.
    My usual boring signature: Nothing

  35. #35
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    If performance matters, get only those columns that are needed. If performance doesn't matter, do whatever you want.
    Agreed!
    Please remember next time...elections matter!

  36. #36
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by TysonLPrice View Post
    Agreed!
    Now you're taking things out of context.
    The debate is if "NEVER use wildcard" is sound advice, and so far nobody has offered up anything technical to offer any validity to that statement.
    Shaggy's statements are somewhat contradictory
    if you are returning ALL the columns from a table, SELECT * has the same performance as writing out the fields
    This in so many cases applies to my case, I generally almost always need all the columns in a table.
    If performance matters, get only those columns that are needed. If performance doesn't matter, do whatever you want.
    this is somewhat contradictory to the former in the condition that I NEED all of the rows.

  37. #37
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: how to use a variable from my DB into my VB.net code

    It isn't a contradiction, it's a subset.

    Here's the rule: "If performance matters, get only those columns that are needed." That is true in all cases. If you need one column, get one column. If you need five columns, get five columns. If you need ALL the columns, get ALL the columns. That will have the best performance in all cases. However, in the subset of cases where you need ALL the columns, then it doesn't matter to performance whether you write out all the column names or use *.
    My usual boring signature: Nothing

  38. #38
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by Shaggy Hiker View Post
    It isn't a contradiction, it's a subset.

    Here's the rule: "If performance matters, get only those columns that are needed." That is true in all cases. If you need one column, get one column. If you need five columns, get five columns. If you need ALL the columns, get ALL the columns. That will have the best performance in all cases. However, in the subset of cases where you need ALL the columns, then it doesn't matter to performance whether you write out all the column names or use *.
    I think kpmc is trolling or being deliberately obtuse just to have fun.
    Please remember next time...elections matter!

  39. #39
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by TysonLPrice View Post
    I think kpmc is trolling or being deliberately obtuse just to have fun.
    I am not trolling. My point is there is no technical data to support the fact I should NEVER use wildcards. It doesnt even qualify as best practice not to use it because it doesnt even apply. I think it's unfounded advice, I think that there are circumstances where you would not use wildcard, but CLEARLY it is poor advice to tell someone to NEVER use a wildcard in your SQL based on your opinion.

  40. #40
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: how to use a variable from my DB into my VB.net code

    Quote Originally Posted by kpmc View Post
    I am not trolling. My point is there is no technical data to support the fact I should NEVER use wildcards. It doesnt even qualify as best practice not to use it because it doesnt even apply. I think it's unfounded advice, I think that there are circumstances where you would not use wildcard, but CLEARLY it is poor advice to tell someone to NEVER use a wildcard in your SQL based on your opinion.
    I'm really done with this post now...
    Please remember next time...elections matter!

Page 1 of 2 12 LastLast

Posting Permissions

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



Click Here to Expand Forum to Full Width