CTE WITH CONDITION TAKING TOO LONG............

Question

CTE WITH CONDITION TAKING TOO LONG............

Anjan@Sql

SSCrazy

Points: 2316
More actions
December 3, 2013 at 9:43 am

#395933

Hi Polks,
;With Cte
as
(
--- some joining query here with 3 tables
--first table has 30 crores records,second one has 6 crores ,third one has 791 records
)
select * from cte
--The above query running in 4 minute 28 sec
;With Cte
as
(
--- some joining query here with 3 tables
--first table has 30 crores records,second one has 6 crores ,third one has 791 records
)
select * from cte where booleancolumn<>0
or
select * from cte where booleancolumn=1
this query is running more than two hours.the column in the where condition dont have any indexes,even no need bcz it has either 0 or 1.
Am i doing anything wrong.pls share your comments.i cant post the exact query.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Ananth@Sql (12/3/2013)
Hi Polks,
;With Cte
as
(
--- some joining query here with 3 tables
--first table has 30 crores records,second one has 6 crores ,third one has 791 records
)
select * from cte
--The above query running in 4 minute 28 sec
;With Cte
as
(
--- some joining query here with 3 tables
--first table has 30 crores records,second one has 6 crores ,third one has 791 records
)
select * from cte where booleancolumn<>0
or
select * from cte where booleancolumn=1
this query is running more than two hours.the column in the where condition dont have any indexes,even no need bcz it has either 0 or 1.
Am i doing anything wrong.pls share your comments.i cant post the exact query.

Start with this article. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 2

Dear Sean

I am not new here, this is my friend's problem.I am bit curious.so considered all these i just posted a psuedocode.sorry for that.can't provide more than that.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Ananth@Sql (12/3/2013)
Dear Sean
I am not new here, this is my friend's problem.I am bit curious.so considered all these i just posted a psuedocode.sorry for that.can't provide more than that.

I know you are not new around here. There is nowhere near enough information posted to offer any kind of solid advice about what might be causing that query to be slow. I could create speculations all day because there are no details to work with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/