Help!!! The SQL statment Tooooo long

Help!!! The SQL statment Tooooo long

Post by feisu » Sat, 24 Oct 1998 04:00:00



I met a serious problem, which is I write a long SQL statment as
follows:
"
SELECT
SUM(FTEFIX1),SUM(FTEFIX2),SUM(FTEFIX3),SUM(FTEFIX4),SUM(FTEFIX5),SUM(FTEFIX6),SUM(FTEFIX7),SUM(FTEFIX8),SUM(FTEFIX9),SUM(FTEFIX10),SUM(FTEFIX11),SUM(FTEFIX12),SUM(FTEFIX13),SUM(FTEFIX14),SUM(FTEFIX15),SUM(FTEFIX16),SUM(FTEFIX17),SUM(FTEFIX18)

FROM OPSPStage WHERE NodeNo in (
7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,26,27,28,29,30,31,32,33,34,35,36,37,38,40,41,42,43,44,45,47,49,51,52,53,54,55,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,109,110,111,112,113,114,115,116,117,118,119,120,121,122,124,125,126,127,129,130,131,132,133,134,136,137,138,139,140,141,142,143,144,145,147,148,149,150,151,152,154,155,156,157,158,159,160,161,162,163,165,166,167,168,169,170,171,172,173,174,176,177,178,179,180,181,182,183,184,185,188,189,190,191,192,193,194,195,196,198,199,200,201,202,204,205,206,207,208,209,210,211,212,213,214,215,216,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250)

"

I use RDO to connect to SQL Server 6.5, the return error is like this:
40002 = S1000: [Microsoft][ODBC SQL Server Driver][SQL Server]Memory
request for         24198 bytes exceeds the size of single page of 2044
bytes.

I can run this SQL without any problem in isql/w. Who can tell my WHY
and HOW to solve this problem.

Any answer will be very appreciated.

Thanks.

SunFei

 
 
 

Help!!! The SQL statment Tooooo long

Post by Neil Pik » Sat, 24 Oct 1998 04:00:00


SunFei,

 Do a SQL Trace to see what RDO is packaging this up in - probably a cursor.  
 Then try this from ISQL/W and hopefully it will fail from there as well.

 Then post a reproduction script here or contact MS PSS.

 (I assume you've already applied SP4 to the server?)

Quote:> I use RDO to connect to SQL Server 6.5, the return error is like this:
> 40002 = S1000: [Microsoft][ODBC SQL Server Driver][SQL Server]Memory
> request for         24198 bytes exceeds the size of single page of 2044
> bytes.
> I can run this SQL without any problem in isql/w. Who can tell my WHY
> and HOW to solve this problem.

 Neil Pike MVP/MCSE
 Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)

 
 
 

Help!!! The SQL statment Tooooo long

Post by Kym Thompso » Wed, 28 Oct 1998 04:00:00


lateral thinking:

        WHERE NodeNo >= 7 and NodeNo <= 250 and not NodeNo in (234 and whatever
else you left out)


 
 
 

Help!!! The SQL statment Tooooo long

Post by M Longmir » Thu, 29 Oct 1998 04:00:00


or...

SELECT
  SUM( FTEFIX1 ), SUM( FTEFIX2 ), SUM( FTEFIX3 ), SUM( FTEFIX4 ),
  SUM( FTEFIX5 ), SUM( FTEFIX6 ), SUM( FTEFIX7 ), SUM( FTEFIX8 ),
  SUM( FTEFIX9 ), SUM( FTEFIX10 ), SUM( FTEFIX11 ), SUM( FTEFIX12 ),
  SUM( FTEFIX13 ), SUM( FTEFIX14 ), SUM( FTEFIX15 ), SUM( FTEFIX16 ),
  SUM( FTEFIX17 ),SUM( FTEFIX18 )
FROM
  OPSPStage
WHERE
  NodeNo between 7 and 24
  and NodeNo not in ( 25, 39, 46, 48, 50, 56, 84, 107, 108, 123, 135, 146,
153, 164, 175, 186, 187, 197, 203, 217, 234 )
;

..sorry if i missed any.


>lateral thinking:

> WHERE NodeNo >= 7 and NodeNo <= 250 and not NodeNo in (234 and whatever
>else you left out)



 
 
 

Help!!! The SQL statment Tooooo long

Post by Isaac Bla » Thu, 29 Oct 1998 04:00:00


When you have an IN clause like yours, put all the values to match in
a separate table ( maybe temporary) and join to it on this column:

SELECT <whatever>
FROM OPSPStage JOIN TheOtherTable ON OPSPStage.NodeNo =
TheOtherTable.NodeNo
WHERE <all other conditions go there>


>SELECT
>SUM(FTEFIX1),SUM(FTEFIX2),SUM(FTEFIX3),SUM(FTEFIX4),SUM(FTEFIX5),SUM(FTEFIX6),SUM(FTEFIX7),SUM(FTEFIX8),SUM(FTEFIX9),SUM(FTEFIX10),SUM(FTEFIX11),SUM(FTEFIX12),SUM(FTEFIX13),SUM(FTEFIX14),SUM(FTEFIX15),SUM(FTEFIX16),SUM(FTEFIX17),SUM(FTEFIX18)

>FROM OPSPStage WHERE NodeNo in (
>7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,26,27,28,29,30,31,32,33,34,35,36,37,38,40,41,42,43,44,45,47,49,51,52,53,54,55,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,109,110,111,112,113,114,115,116,117,118,119,120,121,122,124,125,126,127,129,130,131,132,133,134,136,137,138,139,140,141,142,143,144,145,147,148,149,150,151,152,154,155,156,157,158,159,160,161,162,163,165,166,167,168,169,170,171,172,173,174,176,177,178,179,180,181,182,183,184,185,188,189,190,191,192,193,194,195,196,198,199,200,201,202,204,205,206,207,208,209,210,211,212,213,214,215,216,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250)

 
 
 

1. SQL statment takse a long long time...

hi,

I have a table with 6 fields and 4 millions of records on
MS-SQL Server (2000) on winXP.
I defined a clustered-index which is a compound of the
first four fields.
please look at the following simple SQL statment:

select * from MYTABLE where FIELD1='BLABLA' and
FIELD2='BLUBLU' and FIELD3='BLIBLI'

the result include (the expected) 8 records.
the problem is that it takes about 4 MINUTES!!

it is much more strange than it sounds, because another
SQL statment, which is much
more complex (see below) but actually is a calculation on
a similar set as the previous one,
takes only about 1/10 second.

select * from MYTABLE T1, MYTABLE T2
WHERE
T1.FIELD1='BLABLA' AND T2.FIELD1=T1.FIELD1
AND T1.FIELD2='XXX' AND  T2.FIELD2='YYY'
AND T1.FIELD3=ZZZ AND  T2.FIELD3=T1.FIELD3
AND T1.FIELD4=T2.FIELD4

well...
I looked at the execution plan of the simlpe SQL statment.

the hint on the right one is:

the title is : "clustered index scan"
"scanning a clustered index entierly or only a range"
physical operation   clustered index scan
logical operation    clustered index scan
cost 87%

the hint on the left one is:

the title is: "select"
"retrieves rows from database. allow selection at one or
many columns from one or many tables"
physical operation   select
logical operation    select
cost 13%

I ran the Index Tuning Wizard in the Thorough mode.
as a workload I used a .sql file that contain the simple
SQL statment:
select * from MYTABLE where FIELD1='BLABLA' and
FIELD2='BLUBLU' and FIELD3='BLIBLI'

the wizard recommended to add a simple index on the first
field. i let the
wizard update as he recommended.
now the speed is slow as before but the execution plane
shows on the most left
icon that it uses the non-clustered index that the wizard
created!?

anyway, the compex query run very fast and the execution
plane shows that it uses
the compound index.

the icon it self on the slow query looks deferent than
the on the fast query.
in the SLOW query the icon has a WIDE-STRAIGHT arrow.
in the FAST query the arrow is thin and goes down-right-
down.

the ARGUMENT in the SLOW query is:
OBJECT:([MYTABLE].[SIMPLEINDEX]) WHERE (([MYTABLE].

the ARGUMENT in the FAST query is:
OBJECT:([MYTABLE].[COMPOUNDINDEX]) AS [T1]) SEEK:([T1].
[FIELD1]='BLABLA'AND [T1].[FIELD2]=....

Thanks in advance!

2. Oracle 8.1.5 listener on Linux

3. Need help with sql statment in an odbc statment

4. How can i get only the time in am/pm format from datetime field

5. SQL Group By - Tooooo Slow

6. VB/DAO - Reserved error -7713

7. PLEASE help with SQL statment , slect where joini

8. Standard vs. Enterprise Edition

9. help with where criteria in sql statment

10. Can I use case statments with in where statment

11. Need help with a SQL statment

12. SQL Statment Help