SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question i need a QUERY expert

    I've got this 780line query that i'm porting from oracle to sqlserver. I've already changed what i needed, and am running the query.
    I would also like to optimize the query in order to run it more efficiently.
    there are three subselects and a total of 16 unions and one left outer join.

    what can i do in order to optimize? views wouldn't help, right?

    it will be stored as a stored procedure so that will help some, but i want to optimize the query itself.

    thanks,

    dlg

  2. #2
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't consider myself an expert, but if there's 16 joins and 3 subqueries, the tables probably aren't designed very well. Hard to say without seeing them though....

  3. #3
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually, that's why theres so many subqueries and unions, because the dB is normalized. the query is hitting an Oracle database on an ACD system (pretty much phone logs in a call center)

  4. #4
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What level of normalization do you have though? The experts define it as being a system of ten levels. Most systems are never able to make it past level 6 or 7 as it is almost impossible.
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  5. #5
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the query doesn't make sense to me because all of these unions are referencing the same table, using a where clause that limits the date&time, but the only differences are the fields that it returns, it has aggregate functions on some of these and one last thing, in the where clause the only diff. is that they reference a different "class".

    ???? I'm wondering if these unions can be one query, but then i could face problems with the aggregate functions.

    ????

    dlg

  6. #6
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    Document in English sentences exactly what it is that the query does, from a data-manipulation perspective. Then duplicate that behavior by writing one or more new queries from scratch that meet the requirements. If you're not exactly sure what the query does create a few test cases -- change the data in the database, execute the query, and see what the result set looks like.

    Hope that helps,
    Marshall

    P.S. This approach is called reverse engineering.

  7. #7
    I have an opinion...
    Join Date
    Sep 2001
    Location
    Barrie, Ontario
    Posts
    324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DLG_1
    actually, that's why theres so many subqueries and unions, because the dB is normalized.
    If the DB is normalized, it shouldn't need such a complicated query structure. The reason there are so many subqueries and unions is because the DB is "textbook normalized" which does NO good in the real world. I think you should look into restructuring your DB.

    By the way, Marshall's reverse engineering theory should work...
    Egotist: A person more interested in himself than in me.
    KodeKrash - Eidix - Barrie LUG

  8. #8
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mind explaining, Kode? you have me interested now.
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  9. #9
    I have an opinion...
    Join Date
    Sep 2001
    Location
    Barrie, Ontario
    Posts
    324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Goober:

    I can't really explain too much about textbook normalization (I dropped out of college before that part, and computer science was my minor, not my major), except that I can recount too many times where someone gave me a "normalized db" they made based on textbook learning, and I have rebuilt the db to work faster and easy on the coding end. (Marshall and I have had several in depth discussions to this effect in the past 4 years. -- neither of us seems to "win" either)

    As far as "real world normalization", it is based on a per need deal. There are some basic "rules" that I follow all the time, but they are mostly just common sense, and some of them don't even affect speed, etc.

    1) Use consistant table and column names. I have seen too many tables that are named "UserPrefs" and "UserAccounts" -- I use things like "tblPreference" and "tblUser". Neither one is right or wrong, but when you deal with over 100 DBs containing several tables (at least) per db on any given day, the naming helps. Same goes for columns, use "usrNumber", "usrFullName", "usrPassword", etc, instead of "ID", "Name", "Password". (This is really handy when writing XML straight out of a db -- a whole other subject tho) Using naming structure also eliminates MANY of the issues I have seen people run into when using more than one language to access data from a particular db. One language might ignore a column named "Date" (Date is usually a function in most languages), but another language completely barfs on it.

    2) Put a automatic date/time stamp in every (major) table, or use a separate record tracking table. This is helpful in all sorts of ways, even if you don't plan on needing a date/time for a record. Yes, it increases the size of the table data, but if you index the column, it probably won't affect speed. -- In some cases, this actually SPEEDS UP the queries, like in my headline table for Assimilator, a table that gets about 40,000 - 50,000 new records PER DAY.

    3) Build tables to fit the use AND data, not just the data or the use. (Yeah, yeah, not very helpful, I know.) For example, you may want to use a user registration system to track a mail list in one site, but have a user system to control access via login on another site. When doing a "profile" based system, like the mail list, you could put all your user data in one table, since you probably won't be reading just some of the data, you will want the whole record at once. When using it for a "control" based system, break up the tables, and put things like username, email, password, and full name in one table, then have the rest of the user info in another table. Since most of the work on the user system will be login, you can query a 4 or 5 column table faster than a 12 or 13 column one. The split table also makes it easier to update info, as the profile can be updated without risking the login info if something goes wrong.

    4) Reuse tables if possible. For example, you have tblImage and tblDocument, which contain records for (this is really hard to figure out..) Images and Documents. If you have a column in both tables called "imgType" and "docType" - respectively, reference a third table called "tblType" that contains a list of document AND image types. I worked on a system last week that had 4 tables for information, and 4 tables for information types - 8 tables where I would have used 5, etc. "tblImage", "tblDocument", and "tblType" is MUCH better than "tblImage", "tblDocument", "tblImageType", and "tblDocumentType". (Yes, I spent 2 hrs of my own time rebuilding that system last week, couldn't stand it that way.)


    I could go on and on, I am sure, but you asked kind of a broad question, so I give you a broad answer. If you want an idea of bad db design, look at PHPNuke's db tables.. ICK - no offense to Nukers intended.

    ~~ It is official, this message is my longest ever. ~~
    Egotist: A person more interested in himself than in me.
    KodeKrash - Eidix - Barrie LUG

  10. #10
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your longest, and one of your most helpful to me.

    Thanks, Kode. It's much appreciated.
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)


Bookmarks

Posting Permissions

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