Improving performance on SQL Statement - HOW TO ? 
Author Message
 Improving performance on SQL Statement - HOW TO ?

My server is ORACLE 8.0.5 on Windows NT. Pentium II 350 Mz, 128 Mb RAM

I'm  proving this query on Master-Detail  tables.

Master Table has 27000 rows
Detail Table has 54000 rows

SQL 1)

SELECT DISTINCT
    Detail.ID
FROM
    Master, Detail
WHERE
    Master.ID = Detail.ID AND
    Detail.ASD_NUMERO > 1000
ORDER BY
    Detail.ID

This query return 90% of all rows (26.000 rows) in 13 secs.

SQL 2)

SELECT DISTINCT
    Detail.ID
FROM
    Master, Detail
WHERE
    Master.ID = Detail.ID
ORDER BY
    Detail.ID

This query return 100 % of all rows (27.000) in 5 min 50 secs

What's wrong ? My Server Oracle needs to correct some parameter value ?

TIA

--
Antonio Paglia
e-mail:




Mon, 28 Jun 2004 06:42:40 GMT
 Improving performance on SQL Statement - HOW TO ?

Hi!

I think the DISTINCT is the keyword in this mystery.
Though SQL 2 returns only about 10% more rows than SQL 1,
it may browse through much much more rows than SQL 1
before returning the result. To see if this is true,
execute the commands without the DISTINCT.

Regards,
Janne Kejo

Quote:
>-----Original Message-----
>My server is ORACLE 8.0.5 on Windows NT. Pentium II 350
Mz, 128 Mb RAM

>I'm  proving this query on Master-Detail  tables.

>Master Table has 27000 rows
>Detail Table has 54000 rows

>SQL 1)

>SELECT DISTINCT
>    Detail.ID
>FROM
>    Master, Detail
>WHERE
>    Master.ID = Detail.ID AND
>    Detail.ASD_NUMERO > 1000
>ORDER BY
>    Detail.ID

>This query return 90% of all rows (26.000 rows) in 13
secs.

>SQL 2)

>SELECT DISTINCT
>    Detail.ID
>FROM
>    Master, Detail
>WHERE
>    Master.ID = Detail.ID
>ORDER BY
>    Detail.ID

>This query return 100 % of all rows (27.000) in 5 min 50
secs

>What's wrong ? My Server Oracle needs to correct some
parameter value ?

>TIA

>--
>Antonio Paglia
>e-mail:


>.



Mon, 28 Jun 2004 22:01:29 GMT
 Improving performance on SQL Statement - HOW TO ?
How many detail rows are eliminated by the restriction WHERE
Detail.ASD_NUMERO > 1000? I suspect that Oracle first selects the
appropriate rows from the detail table, then joins them to the master
table, and finally eliminates the duplicates. Every extra detail row
included in SQL 2 would therefore require an extra 27000 comparisons in the
join, with some subset of that number being included in the result.

You might try using the Oracle EXPLAIN PLAN command to see what it's
actually doing in the different situations-if it's doing anything
differently. It might also be using an index to select from Detail table in
SQL 1. It won't use an index in SQL 2 because you're selecting everything
from the Detail table anyway.

Quote:

> My server is ORACLE 8.0.5 on Windows NT. Pentium II 350 Mz, 128 Mb RAM

> I'm  proving this query on Master-Detail  tables.

> Master Table has 27000 rows
> Detail Table has 54000 rows

> SQL 1)

> SELECT DISTINCT
>     Detail.ID
> FROM
>     Master, Detail
> WHERE
>     Master.ID = Detail.ID AND
>     Detail.ASD_NUMERO > 1000
> ORDER BY
>     Detail.ID

> This query return 90% of all rows (26.000 rows) in 13 secs.

> SQL 2)

> SELECT DISTINCT
>     Detail.ID
> FROM
>     Master, Detail
> WHERE
>     Master.ID = Detail.ID
> ORDER BY
>     Detail.ID

> This query return 100 % of all rows (27.000) in 5 min 50 secs

> What's wrong ? My Server Oracle needs to correct some parameter value ?

> TIA

> --
> Antonio Paglia
> e-mail:





Tue, 29 Jun 2004 01:22:28 GMT
 Improving performance on SQL Statement - HOW TO ?
Did you view your explain plan?  This will tell you exactly what Oracle is doing with your query.  Your 2nd query is probably doing a full table scan on both tables, where the 1st is using an index on ASD_NUMERO.   Just a guess, but seeing your explain plan would tell me.

My server is ORACLE 8.0.5 on Windows NT. Pentium II 350 Mz, 128 Mb RAM

I'm  proving this query on Master-Detail  tables.

Master Table has 27000 rows
Detail Table has 54000 rows

SQL 1)

SELECT DISTINCT
    Detail.ID
FROM
    Master, Detail
WHERE
    Master.ID = Detail.ID AND
    Detail.ASD_NUMERO > 1000
ORDER BY
    Detail.ID

This query return 90% of all rows (26.000 rows) in 13 secs.

SQL 2)

SELECT DISTINCT
    Detail.ID
FROM
    Master, Detail
WHERE
    Master.ID = Detail.ID
ORDER BY
    Detail.ID

This query return 100 % of all rows (27.000) in 5 min 50 secs

What's wrong ? My Server Oracle needs to correct some parameter value ?

TIA

--
Antonio Paglia
e-mail:




Tue, 29 Jun 2004 02:17:54 GMT
 Improving performance on SQL Statement - HOW TO ?
Ok, this problem is solved.
EXPLAIN PLAN command show the index used in SQL1. For that reason, SQL is
faster than SQL2 that does not use some index.

Now, I have the following problem:

Assume that :
    - DBSConnectionString is a correct Connection string to my DataBase
    - SQL statement is the same in both cases.
    - "Fetch" (in the Case 2) is a method of stateless object that is
running on MTS and return a disconnected
       recordset (and work fine). The code defined in this method is
<similar> to the Case 2 code
    - Oracle use the Primary Key (listed with EXPLAIN PLAN command). The PK
has 3 fields and
       Detail.ASD_NUMERO  is the middle field. For ex:
       Primary Key is : Field1 + Detail.ASD_NUMERO + Field3
       where Field1 is VARCHAR(10)
                 Detail.ASD_NUMERO is NUMBER(9)
                 Field3 is NUMBER(5)

Well, now considers

'//  Case 1  (works fine)

Dim RST As ADODB.Recordset
Dim SQL As String

 SQL = "SELECT DISTINCT " & _
                 "Detail.ASD_NUMERO  " & _
            "FROM  " & _
                 "Detail " & _
            "WHERE  " & _
                "Detail.ASD_NUMERO > 1"

Set RST = New ADODB.Recordset
RST.CursorLocation = adUseClient
RST.Open SQL, DBSConnectionString, adOpenForwardOnly, adLockReadOnly
....
set RST.ActiveConnection = Nothing
RST.close

----------------------------------------------------------------------------
----------------
'// Case 2

 SQL = "SELECT DISTINCT " & _
                 "Detail.ASD_NUMERO  " & _
            "FROM  " & _
                 "Detail " & _
            "WHERE  " & _
                "Detail.ASD_NUMERO > 1"

'// Fetch: return a disconnected recordset
 Set RST = objMTSObject.Fetch(SQL)   '(I gets an error)

----------------------------------------------------------------------------
----------------

Well, running Case 1 code in a new project works fine but running Case 2
code in a new project I get this error:

"Error in the method ' Fetch' of the object ' _ clsClassName'

I have tried to:
- Execute the sql without DISTINCT (works fine in both cases)
- Executing

 SQL = "SELECT DISTINCT " & _
                 "Field1, Detail.ASD_NUMERO  " & _
            "FROM  " & _
                 "Detail " & _
            "WHERE  " & _
                "Detail.ASD_NUMERO > 1"

That is, adding the first field of the PK before Detail.ASD_NUMERO field,
and also works fine in both cases

- Executing

 SQL = "SELECT DISTINCT " & _
                 "Detail.ASD_NUMERO, Field3  " & _
            "FROM  " & _
                 "Detail " & _
            "WHERE  " & _
                "Detail.ASD_NUMERO > 1"
That is, adding the third field of PK after Detail.ASD_NUMERO field, and
also works fine in both cases

But SELECT DISTINCT with only Detail.ASD_NUMERO field in the select clause
does not works

Someone can tell me the reason of this behavior ?

TIA
Antonio



Sat, 03 Jul 2004 04:18:57 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. how to improve performance of one SQL statement

2. Similiar SQL statement, performance improve?

3. Improving Performance of insert statements

4. Improving performance of INSERT statements

5. HOWTO: Improve SQL 6.5 Performance for very simple SQL

6. Question on improving SQL statement syntax

7. Improved performance with MS-SQL server?

8. Improving performance of Web server, SQL server

9. improve SQL "insert" performance

10. how to improve query performance wihout using dynamic sql

11. Improving ADO performances against SQL Server?

12. Improving SQL*Net performance


 
Powered by phpBB® Forum Software