Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

Slow Sql Query Needs Improving, Could Sub Queries Be My Answer.

SQL form

  • Please log in to reply
2 replies to this topic

#1 mctim


    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 110 posts

Posted 30 July 2012 - 05:20 PM

I'm obviously a little in the dark here about what to do. You could literally fill an aircraft carrier w/ all that I dont know about sql, so bare with my if my question is poorly structured.

Lets say I have 3 tables: orders, order items, and items
Orders is a table that describes orders obviously. It has information such as order date customer id ect. Items is a table that describes items it contains info such as product name and inventory. Order items could be compared to a shopping cart it basically describes all the items the order was placed for. So far so good, right?

Well I need to make a reporting tool that will basically query the order items table and give me all of the order items that are for a given item and happened between a given time frame. Just to reiterate, before the query starts I know: the item title and a timeframe I'm interested in.

so here's my query currently in sort of psuedo-sql

select * from Order_Item
where Order.orderDate <= ?
and Order.orderDate >= ?
and Item.name in (?)

This query works great it gets everything that I need until I try to run it on my production environment. Once I do that, it about 40 seconds to run. Now let's talk about indexes. The order table has an index for orderDate. The item table has an index for name, and the order_item table has an index for (orderId, itemId). Now obviously there's a huge red flag about this query in terms of optimization "select *." For all intents and purposes, lets ignore that fact. My real question is is there anything I could do to the query itself to increase my runtime performance.

I really don't know how to say this the right way, but adding indexes to the tables is an option. Ergo, if there are any suggestions on that front I'm all ears.

I just realized how bad my title is. I cant figure out how to change it though. Sorry guys.
  • 0

#2 WingedPanther73


    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 31 July 2012 - 07:36 AM

Are you doing joins for this? It's really hard to guess what the issue might be when the tables in the where clause don't appear in the from clause. Another question is: what is the data volume you're working with? 40 seconds against a million records sucks. 40 seconds against 100million records isn't too bad.

What's the backend database? Are you doing a blocking or non-blocking query? MS SQL Server has an option "With no-lock" that lets queries run faster, but they aren't guaranteed to get data in a 100% valid state.

As a general rule, subselects run slower than joins, but it does depend on the nature of the data as to whether a join is a viable option.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/

#3 lespauled


    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 31 July 2012 - 08:11 AM

It's deinitely a multi-table join, but the join in missing. Asuming you have the correct structure, something like this should work:
SELECT * FROM Order_Item oi
where o.orderDate <= ? and o.orderDate >= ? and i.name in (?)
But I don't understand why you would want * from ORDER_ITEM. I'm guessing that you would want things from orders and items.
  • 0
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

Also tagged with one or more of these keywords: SQL, form

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download