query is abending because of time--Very urgent

[raj]

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

Mkae sure you got a table scan or not.

If you do, create a non clustered index would help speed up.

------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

[Sushruth Nanduri]

If you have no where clause in your query, a table scan will be performed.
Try to use a where clause which contains the column on which the clustered index has been defined.
Your query will speed up.
Alternatively , consider creating a non-clustered index and use it in the where clause.

regards
Sush.


------------
at 4/25/00 10:58:14 AM


Mkae sure you got a table scan or not.

If you do, create a non clustered index would help speed up.

------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

[Sushruth]

Forgot to mention onething.
Consider checking the query governor cost limit.
To check this, go to server properties and select server settings and
look at server behaviour and see whether there has been any limit that
has been set up before.
Manipulate this if you want.

good luck
Sush.


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

[raj]

Thank u all,
But there is no primary key on my table, query is taking so much time.
If i create primary key, is it the answer for my question.
pls answer..

raj


------------
Sushruth Nanduri at 4/25/00 11:41:52 AM

If you have no where clause in your query, a table scan will be performed.
Try to use a where clause which contains the column on which the clustered index has been defined.
Your query will speed up.
Alternatively , consider creating a non-clustered index and use it in the where clause.

regards
Sush.


------------
at 4/25/00 10:58:14 AM


Mkae sure you got a table scan or not.

If you do, create a non clustered index would help speed up.

------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

[raj]

i want to use query without using index, do i need to set any parameter for not to use index.
COULD u pls suggest me
raj


------------
Sushruth at 4/25/00 12:14:35 PM

Forgot to mention onething.
Consider checking the query governor cost limit.
To check this, go to server properties and select server settings and
look at server behaviour and see whether there has been any limit that
has been set up before.
Manipulate this if you want.

good luck
Sush.


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

if u don;t want to use index, use an index indicator of 0 on the query. Like this:

select * from table(0)

This will bypass any indexes.

------------
raj at 4/25/00 1:42:54 PM

i want to use query without using index, do i need to set any parameter for not to use index.
COULD u pls suggest me
raj


------------
Sushruth at 4/25/00 12:14:35 PM

Forgot to mention onething.
Consider checking the query governor cost limit.
To check this, go to server properties and select server settings and
look at server behaviour and see whether there has been any limit that
has been set up before.
Manipulate this if you want.

good luck
Sush.


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

[Humberto Velez]

Raj, First of all make sure the keys that are defined in your clustered index are the same columns that you are using in your where clause. i.e.

SELECT col1, col2, col3, col4
FROM table1
WHERE
col1 = 'value1' and
col2 = 'value2' and
col3 = 'value3'

Your clustered index should include these 3 columns in the order in which you will execute the query. If this still doesn't work make sure you don't have fragmentation in the table by DBCC SHOWCONTIG (table_name). Check in the scan density row this should read 100%[1:1] if not, rebuild your clustered index and try again. If this still doesnt work e-mail me your query and your table definition

Humberto

[raj]

i am not using any where class, just simple select list of columns from table
it is using clustered index. Taking so much of time.pls suggest me.
Thank you for ur suggestions.
--raj


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

select * from table(0)

This will bypass any indexes.
if i am aliasing the query then what is the syntax to bypass the queries



------------
at 4/25/00 2:18:46 PM


if u don;t want to use index, use an index indicator of 0 on the query. Like this:

select * from table(0)

This will bypass any indexes.

------------
raj at 4/25/00 1:42:54 PM

i want to use query without using index, do i need to set any parameter for not to use index.
COULD u pls suggest me
raj


------------
Sushruth at 4/25/00 12:14:35 PM

Forgot to mention onething.
Consider checking the query governor cost limit.
To check this, go to server properties and select server settings and
look at server behaviour and see whether there has been any limit that
has been set up before.
Manipulate this if you want.

good luck
Sush.


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

[Humberto Velez]

Raj,

Are you trying to do an "ORDER BY" or "GROUP BY" clause ? if not, then I suggest you drop the clustered, since the query will make a 'table scan' regardless.

How many rows are you trying to retrieve ? have you tested the same query from the "query analyzer" window ? try using the execution plan.

[Sushruth]

Hi Raj !
Regarding your problem, as long as you are not using any index,
there is no solution except that it performs a table scan.
First of all there is no reason why you want to do a select * , on a
table that has 900000 records.You shpuld never do that...
Try creating an index on one of the columns you are selecting.
If you can't, put this in the where clause: "where 1=1"
and try...That may help or may not.Let me know what is happening.
regards
Sush.


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

[Sushruth]

You said that you are usin a clustered index.
Try using it somehow in the where clause.
Fo example if you have non zero values in the column on which the
CI was used use "where CI column != 0"
if it has no spaces then use "where CI column != "" "
regards
Sush.


------------
raj at 4/25/00 4:52:22 PM

i am not using any where class, just simple select list of columns from table
it is using clustered index. Taking so much of time.pls suggest me.
Thank you for ur suggestions.
--raj


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

see,
they created clustered index on 9 columns, in which one colmn is having nulls also. my user got a problem for this query. i dont know what to do. i am just rebuilding cluster index. do u got any idea how many columns we can use in clustered index.

tahnk u
raj


------------
Sushruth at 4/26/00 9:28:21 AM

Hi Raj !
Regarding your problem, as long as you are not using any index,
there is no solution except that it performs a table scan.
First of all there is no reason why you want to do a select * , on a
table that has 900000 records.You shpuld never do that...
Try creating an index on one of the columns you are selecting.
If you can't, put this in the where clause: "where 1=1"
and try...That may help or may not.Let me know what is happening.
regards
Sush.


------------
raj at 4/25/00 10:54:21 AM

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj