filter rows after select

[olisemalis]

Hello,

I would like to have an sql query in a storeed procedure that returns some records from a table and then filter those records (after get them from a table) based in a list of values. What is the best way to achieve this?

For now I have this:


select fields from table weher field1="" and fieldn ="" and fieldx in (list of values) --> with this I´m getting frequent timeouts. Because the select without the list of values will return very few rows, I would like to apply the filter of the values list just after getting the rows from the select.

Thank you

Thank you

 

[vbkris]

>>Because the select without the list of values will return very few rows, I would like to apply the filter of the values list just after getting the rows from the select

sorry, i dont get you. can you provide us with some sample data / output???

Known is handfull, Unknown is worldfull

 

[TysonLPrice]

Do you mean something like:

select * into #TempTable
fields from table weher field1="" and fieldn =""

Then

select fields from #TempTable
Where fieldx in (list of values)

When you are done with the (list of values) query

Then

Drop table #TempTable

 

[olisemalis]

Tyson,

Something like that, but do wu must to use a temporary table? the point is that the in clause will only filter some rows of those already returned using the other clauses. These clauses runs in about 3 s, but with the in cluase the select takes 20s to run! So, the point was to make the select without the in clause and then filter the result set with the in clause.

Isn´t a table data value better than a temporary table? If I apply the in clause in a having clause, shouldn´t sql server make the select without the in clause, and just after getting the recordset apply the having clause? Maybe this could do the trick?

Thank you

 

[Crowley16]

have you thought of using a derived table?

select * from (
select * from tableName where someCondition
) as A
where A.col IN (list)

--------------------
Procrastinate Now!

 

[TysonLPrice]

Isn´t a table data value better than a temporary table?

That depends on what you are doing. My thought was to use a temporary table so you could "play" with the in clause against it. I was thinking this was a research kind of effort. Sounds like you are setting up something that will run as part of an app or something. If so I don't see how you can save any time on the select that runs so long because you "have" to do it. Do you have or can you use indexes?

Maybe an index hint whould help. Check out this:

http://www.sql-server-performance.com/hints_table.asp

 

[Crowley16]

the reason is because the in clause makes the query non-sargable, therefore cutting down the records then doing the in filter will reduce time taken...

it is kinda strange that query plan doesn't automatically do this, but using a derived table should force this behaviour...

--------------------
Procrastinate Now!

 

[olisemalis]

Crowley16,

Hi thought on that. My ideia was that SQL Server would always evaluate first the select clause inside the () first. . However, I made a test in a small table and I realize that SQL Server as the inteligence to join the where clauses of both Selects...sight! Ok, maybe it made this because the table was very small and the statistics it have point it to make everything at once, but I´m affraid that sometimes e can make something like this in the bigger table. So, I would like to have sure that sq server would always make the select inside the () first and that it would take the returned rows to make the outside where clause.

Thank you

 

[Crowley16]

then you'd have to use a temp table or table variable...

--------------------
Procrastinate Now!