Post reply

Need help on a Sql Query

  • July 16, 2012 at 12:30 pm

    #1513253

    Brandie Tarvin (7/16/2012)

    SQLKnowItAll (7/16/2012)

    Brandie Tarvin (7/16/2012)

    The asterick isn't helping you much, either. Try breaking that down to individual column names.

    Although I am not a fan of using *, how will eliminating that improve performance? I have not heard of this.

    First, the caveat: performance issues depends greatly upon your database and table schemas. You might never see performance issues using the *.

    That being said, when you use an *, SQL has to go to the table before executing the query to see what columns are available. Again, depending on your schema, this could take time. And depending on the query (how many tables, views, indexed views are involved), it could take long enough to actually show as a performance hit. EDIT: If you list the columns, though, SQL only has to verify the exact columns are there, and it doesn't have to pull back the data immediately of all columns on the table. * means it's got to pull everything, regardless of whether or not you're using that data. And that really shows in SSIS, SSRS, and SSAS, BTW. Of course, I haven't tested 2k8 to see if the same performance issues exist that existed in 2k5.

    The last issue with using * is the one that really counts in my book. If you use * in a view or function or proc, then change the underlying table schema, the cached data can cause failures because the information the object is expecting back is different from what is really there. Granted, you can't really call this a performance issue, but when I have to take 4 hours out of my work day to drop and recreate views because someone changed a table schema, I most certainly will be ranting about lost time to anyone in earshot. And this last one has happened to me often enough, that I won't let anyone write code that uses * at my work place.

    Interesting. I always thought of this as more of a pipeline thing and thought of it as having no effect on execution. I can even see differences in the execution plan when doing SELECT 1, SELECT columnA, columnB, and SELECT *. Although the plan itself does not change, the Subtree Cost of the SELECT does. Good to know! Thanks!

    Jared
    CE - Microsoft

  • July 17, 2012 at 5:08 am

    #1513511

    Just an update , created non clustered index on column SessionId in both the tables, i am getting results in about 1.5 minutes now.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply