Improve performance of SQL Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Improve performance of SQL Statement

Author

Topic

buckwheat
Starting Member

3 Posts

Posted - 2004-09-29 : 18:16:02

I have been requested to improve the performance and speed of this SQL statement.

SELECT count(*)
from tp_entity_rel t, entity_formats e
where t.unique_id = e.unique_id
and t.eccom_tp_id = :b16
and t.isa_tp_id = :b15
and t.gs_submitter_id = :b14
and t.payer_id_lob = :b13
and (t.entity_number = :b12 or
t.entity_number = :b11 or
t.entity_number = :b10 or
t.entity_number = :b9 or
t.entity_number = :b8 or
t.entity_number = :b7 or
t.entity_number = :b6 or
t.entity_number = :b5 or
t.entity_number = :b4)
and e.message_format = :b3
and e.test_prod_ind = :b2
and to_date(:b1, 'YYYYMMDD') >= e.prod_from_dt
and to_date(:b1, 'YYYYMMDD') <= e.prod_thru_dt

I know a few items that can be improved, but I would like anyone's help to rewrite this statement to improve performance. Any tips / suggestions would be appreciated.

Thanx,
Buckwheat

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-29 : 18:22:23

Could use t.entity_number In( , , , )
Could also move the
where t.unique_id = e.unique_id
To
from tp_entity_rel t Inner Join entity_formats e On t.unique_id = e.unique_id

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-29 : 18:24:27

If you can evaluate the dates from >= to values that would be > Or < that would make the where clause faster... I am guessing you have evaluated your indexes

Surf On Dude!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-29 : 18:39:07

What dbms is this? :b8? That doesn't look like MS SQL Server. What indexes do you have on your tables to support this query?

Tara

scullee
Posting Yak Master

103 Posts

Posted - 2004-09-29 : 19:24:29

Looks like Oracle code to me

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-09-29 : 19:50:39

this is oracle.

here are my suggestions:

1. use an IN list for your t.entity_number
2. make sure you have an index on all joined keys
3. Make sure you have indexes on the columns in the where clause that are the most unique. Looks like payer_id_lob and to_date (maybe prod_from_dt and prod_thru_dt also) might be good candidates just based on their names, but you haven't given us much info so I don't really know.

can you post some tkprof output for this? Actually, it would be best if you moved this thread over to the Oracle discussion area at dbforums.com. I'd still like to see your tkprof output, or enhanced explain plan (SQLTXPLAIN metalink article 215187.1) output.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-09-29 : 19:52:11

quote:
Originally posted by tduggan

What dbms is this? :b8? That doesn't look like MS SQL Server.

Those are Oracle bind variables.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-29 : 19:54:47

Ah thanks.

In SQL Server, IN gets converted to OR:

SELECT * FROM Table1 WHERE Column1 IN ('A', 'B')

gets converted to:

SELECT * FROM Table1 WHERE Column1 = 'A' OR Column1 = 'B'

Does the same hold true for Oracle?

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-09-29 : 20:33:48

quote:
Originally posted by tduggan
In SQL Server, IN gets converted to OR:

<SNIP>

Does the same hold true for Oracle?

Yes, the CBO would evaluate the IN-list or multiple OR statements the same way. However, I believe oracle has some optimizations with IN-lists when they are combined in a more complicated SELECT (like the one above). I'd have to do some test cases to be sure though.

The main reason for me would be to make the code a little more legible. You would then have fewer lines to optimize ;)

-ec

buckwheat
Starting Member

3 Posts

Posted - 2004-09-30 : 11:32:20

Thanx to everyone for your help. I at a real disadvantage with this statement, so I do not have much more info to offer. Yes, this statement is for Oracle, but a lot of stuff is compatible between SQL and Oracle. I was given this statement by one of our vendors and asked if I could improve it. I do not have any of the Oracle utilites you mentioned. And with the SQL, I have access to just a few utilities. I help with the dba work here as much as I can. I have some SQL dba experience, but I do not have a lot of time to get some options to fix this. I will try your suggestions and will post this to the Oracle discussion group, I just didn't know the link before.

If anyone has more options or more help please feel free to let me know.

Thanx a million,
Buckwheat

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-09-30 : 11:54:31

quote:
Originally posted by buckwheat

Thanx to everyone for your help. I at a real disadvantage with this statement, so I do not have much more info to offer. Yes, this statement is for Oracle, but a lot of stuff is compatible between SQL and Oracle. I was given this statement by one of our vendors and asked if I could improve it. I do not have any of the Oracle utilites you mentioned. And with the SQL, I have access to just a few utilities. I help with the dba work here as much as I can. I have some SQL dba experience, but I do not have a lot of time to get some options to fix this. I will try your suggestions and will post this to the Oracle discussion group, I just didn't know the link before.

Any way to get an explain plan for this thing? We really have no way of helping you unless we know a couple of things.

1. The table definitions for the underlying tables
2. The size of the underlying tables
3. The index definitions for all pertinent tables
4. an explain plan showing what choices the cost based optimizer used to return the data.
5. It would also be helpful to know some settings in your init.ora file as well as what release of oracle this is running on.
6. Is the database running in rule based or cost based mode? How current are the table/index statistics?

The easiest and best way to get all this information is to use the SQLTXPLAIN tool I mentioned above. Is there anyway you can have someone us that tool and post or email the output?

Without that info, nobody will be able to help you on this.

if you want, we can continue this conversation in email. Just let me know.

-ec

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 12:35:28

I'm buckwheat...damn it.....ah...when Eddie Murphy was funny

What version of Oracle?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 12:41:47

You sure this is Oracle?

Looks more like UDB....and those would be host variables in a program...

Can you show us the DDL and the INDEXes?

Brett

8-)

buckwheat
Starting Member

3 Posts

Posted - 2004-09-30 : 17:03:46

This is what I can get:
Tables:
tp_entity_rel (27,515 rows of data)
UNIQUE_ID NOT NULL VARCHAR2(10)
ECCOM_TP_ID NOT NULL VARCHAR2(12)
ISA_TP_ID NOT NULL VARCHAR2(15)
GS_SUBMITTER_ID VARCHAR2(15)
ENTITY_NUMBER NOT NULL VARCHAR2(30)
ERA_ID_ISA08 VARCHAR2(15)
ENTITY_TYPE VARCHAR2(5)
ERA_GS02 VARCHAR2(15)
PAYER_ID_LOB NOT NULL VARCHAR2(5)
STATE_CODE VARCHAR2(2)
ERN_INDICATOR VARCHAR2(1)
PRINT_TO_MAIL_IND VARCHAR2(1)
STOP_PAY_CODE VARCHAR2(2)
MEDA_CHAIN_ID VARCHAR2(6)
OTHER_MSG_1 VARCHAR2(1)
OTHER_MSG_2 VARCHAR2(1)
OTHER_MSG_3 VARCHAR2(1)
OTHER_ID_ISA08 VARCHAR2(15)
OTHER_ID_GS02 VARCHAR2(15)
CHANGE_USERID VARCHAR2(12)
CHANGE_DATE DATE

entity_formats (60,227 rows of data)
UNIQUE_ID NOT NULL VARCHAR2(10)
MESSAGE_VERSION VARCHAR2(15)
MESSAGE_FORMAT NOT NULL VARCHAR2(12)
TEST_PROD_IND VARCHAR2(1)
INBOUND_OUTBOUND_IND VARCHAR2(3)
SEND_TO_TP_ID VARCHAR2(15)
CONTRACT_RECVD_DT DATE
TEST_COMPL_DT DATE
PROD_FROM_DT DATE
PROD_THRU_DT DATE
PROD_REP VARCHAR2(30)
CHANGE_USERID VARCHAR2(12)
CHANGE_DATE DATE

The UNIQUE_ID is the main index on both tables.

Oracle version is 9.2.0

That's about all I can get.

Thanx,
Buckwheat

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-09-30 : 17:42:57

I think the best thing you can do is put a few indexes on your tables.

Try putting an index on prod_from_dt and prod_thru_dt for the entity_formats table. Also put an index on entity_number in your tp_entity_rel table. Also, make sure that you have single column indexes on all of the keys that you join on.

You might have to play around with the index on the date columns a bit. The order which you create the index might be important, depending upon your where clause of other queries. You may want to create two indexes, the 2nd one with the columns listed in the opposite order.

the effectiveness of these indexes really depends on the cardinality of the columns. Is that information known?

have them turn timing on and get a baseline measurement of your query. Do this a couple of times and get the average. Then add the indexes one at a time and look to see what the timings change to. Make sure you run them a couple times apiece, so you account for the cache warming up.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources