Showing posts with label complex. Show all posts
Showing posts with label complex. Show all posts

Wednesday, March 21, 2012

Performance Complex SQL Issue

Hi,
I have a table ORDER_DETAIL with 22 million rows which has an index
of
(person_id, code_id, created_dtt)

I have another ORDER table with 5 million rows which has an index
of
(order_dtt, person_id)

I have a small CODES table with 1000 rows which allows me to get
the 50 or so codes I need. My query needs to be something like this:

select od.person_id, od.code_id
from order_detail od, order o, codes c
where o.order_dtt between sysdate-365 and sysdate
and o.person_id = od.person_id
and od.code_id in (select code_id from codes where code_type =
'MYCODE')
and od.create_dtt between sysdate-365 and sysdate

But is this using the full index on the ORDER_DETAIL table? Should I
be using EXISTS in some fashion instead?

Accessing the ORDER_DETAIL table is a pain because it is so large, the
code_id's I need are a relatively small number but the date range is
about 25% of the table - same with the ORDER table. However it is the
only way I can get to filtering down to the code_id.

This is a simplification of the problem - but accurate - adding
additional indexes is not an option.

thanks!

Tim[posted and mailed, please reply in news]

Tim Smith (timasmith@.hotmail.com) writes:
> I have a table ORDER_DETAIL with 22 million rows which has an index of
> (person_id, code_id, created_dtt)
> I have another ORDER table with 5 million rows which has an index of
> (order_dtt, person_id)
> I have a small CODES table with 1000 rows which allows me to get
> the 50 or so codes I need. My query needs to be something like this:
> select od.person_id, od.code_id
> from order_detail od, order o, codes c
> where o.order_dtt between sysdate-365 and sysdate
> and o.person_id = od.person_id
> and od.code_id in (select code_id from codes where code_type =
> 'MYCODE')
> and od.create_dtt between sysdate-365 and sysdate
> But is this using the full index on the ORDER_DETAIL table? Should I
> be using EXISTS in some fashion instead?

First, what is sysdate supposed to be? I ask because there is nothing
called sysdate in MS SQL Server. While this forum is for MS SQL Server,
it has happened before, that people have asked questions that have
applied to other database engines. While some SQL questions are fairly
generic, performance questions are often engine specific, because
different DB engines uses difference strategies.

The query as written contains a superfluous occurance of codes in
the FROM clause. This could lead to a cartesian join between codes
and the rest of the result set.

The simplest way to involve codes in the query would be:

select od.person_id, od.code_id
from order_detail od, order o, codes c
where o.order_dtt between sysdate-365 and sysdate
and o.person_id = od.person_id
and od.code_id = c.code_id
and c.code_type = 'MYCODE'
and od.create_dtt between sysdate-365 and sysdate

Now, assuming that you are using MS SQL Server 2000, I think you would get
better performance without involving the orders table at all. Had you been
able to narrow down the range to say 1% of the table with the condition on
orders.order_dtt, it would be another issue.

To join orders and order_details, there are three different join
strategies that SQL Server can use Nested Loops, Merge Join and
Hash Join. Nested Loops, means that for each matching rows in
Orders, look up a row in Order Details. When you look up 25% of
the rows, you will access several data pages more than once, and
you will get more reads than for a plain table scan on Order Details.

Merge Join and Hash Join both involves scanning the tables, but only
doing it once. I don't know if a merge join is possible in this case.

Of course, here I am discussing the example query you posted. The
actual query you have may be different.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Monday, March 12, 2012

Performance Advice

Hi,

The app I work on has a few complex queries (selects with 5-9 tables joined, data volumes of 200,000 rows +). These typically tend to aggregate info from the tables in question. They work on large volumes of data. We also have some stored procs that works on several rows of data (again in large volumes) and perform writes else where.

Overall the tables that these two types of queries run on contain millions of rows of data, and they normally work on a subset that is also substancially large - 100, 000 rows for example.

The app I work on is some sort of a tool(decision support type) and we dont expect that these queries be used like typical transaction processing systems. They will probably only be used by a few people at a time (1-3).

I've noticed that running this feature on the dev db box (2 GHZ Desktop PC with 1 GB ram) the CPU spikes to 100% and the DB server starts refusing connections to other users. Overall untill the long running queries are completed the DB may not be used by any other user.

I've tried to run the same app on a multi proc server config box. What I find is that such queries still use one of the CPUs 100%. But connections are not refused and other users can still use the database probably because of the other procs on the box.

Although this is a somewhat odd question - what does this mean ? does it mean that the query is poorly written ? Yes it is complex and we've tried to tune it to the extent possible - indexes, join sequences, intermediate materilization etc.

is it wrong to throw hardware at such a problem ? How much more might the query be tweaked ? and what will be the threshold level after which the query may not be tweaked anymore and needs better hardware ? How does one determine this ?

What I'm hoping to get with the above questions are just pointers for me to explore further and learn how I should proceed.

Any suggestions will be great.

Thanks,

Avinash

Hello,

I am not sure whether you are using MAX DOP option in your query running in production on multiple CPU's. Please look up "Degree Of Paralellism" in BOL.

I hope this helps....

Thanks.....

|||

Do any other applications use the database(s) on this server? Are they affected by your complex queries?

If performance turns out to be a serious problem, and if you tend to frequently aggregate your data, then you could consider using Analysis Services as part of your solution.

Chris

|||

Hello Chris,

Many thanks for your response. I dont have an exact answer to your question because what we build is a product and it may very well be installed on a shared database server.

But what I have seen in general is that the complex queries eat up a 100% of just one CPU per user and the number of CPUs being used 100% goes up with additional users performing the complex query. In my scenario, we dont expect any more than 1 or 2 users running such queries. If the app is running on a quad board machine, other database users will not be absolutely stranded. That is assuming that the other database users dont have similar demands from the database.

I've seen other Transaction Processing kind of apps work allright on another database on the same database server. Each complex query chews up one processor from what I've seen.

Honestly - I'm not conversant with analysis services. I will look into it. But I was hoping to get some ideas on capacity planning and determining what may be the base hardware requirement to service our needs knowing/having some idea of the volume we work with.

Any ideas will be appreciated.

Thanks,

Avinash

|||hi,
firstly i think if you use long running transactions you should consider Windows Workflow Foundation.by coincidence i have searched about this condition. i have a database which has a table with an average of 3 million rows and a relative table which has a 1,5 million rows and lastly a relative table has a 2 million rows. so i thought about what can i do. despite i have a server which has lower system capacities than yours, my server's cpu never spikes to 100%. i think there is a problem in your sql statement. once i had 2 table with 100 000 and 250 000 rows and as yours my server crashed too, i looked at task manager and saw that this query increased memory to 7 GB! anyway i suggest you to use index tuning wizard. if you didnt index your tables correctly you should do that right now. but if you indexed your tables then you should consider partitioning on tables. as i said before i execute long queries and i get response between 0-1 second! if you clarify your queries functionality then i can help you.(e.g are they insert, update or select queries?)
|||

Hello Ibrahim,

My queries are mostly complex selects with a whole bunch where conditions. They typically aggrregate data across several thousands of rows and they need to sift through millions of rows to find the subset of data.

But also I have some inserts - but they have additional read logic which again means selects with complex conditions and aggregations.

The point that I seem to have accepted is - when you say "as i said before i execute long queries and i get response between 0-1 second! if " that cannot be an absolute statement right ? In the sense that at some point your needs will require better hardware. It cannot be that any need can be satisfied by existing hardware. What I'm trying to understand is how do you determine that for your needs, X type of hardware is non negotiable.

Thanks,

Avinash

|||In my past experience, I split a complex join statement to multiple small statement. That will reduce CPU usage. Also you can review your SQL statement to find out whether there has some logic error to reduce number of temp records generated.