Need some advices on SQL INTERSECT Function

[James Shao]

Hi guys, I am currently trying to create a powerful stock market scanner, like the one Yahoo or Google has.   The scanner will allow users to choose among some 50 parameters to scan through several thousand stocks. I am not sure how Yahoo or Google does it, but the only way I know is by using a SQL INTERSECT query.   The query statement probably needs to be reconstructed each time an user adds additional parameter.   So when a parameter is added, I'll simply append "INTERSECT [new sql query goes here]" into one big long query string.   ;) Conceptually, it'll be like this: SQL Query 1 (parameter 1) INTERSECT SQL Query 2 (parameter 2) INTERSECT SQL Query 3 (parameter 3) INTERSECT .. .. Is this method the best approach to create a scanner?   I thought all other methods (INNER JOIN, Views, CTEs, nested query) would result in over-complicated query strings. Or is there a better method (the scanner in Yahoo/Google seems to run really fast)?   :rolleyes: I'd greatly appreciate any suggestions!!   Thanks as always!

[Dimitri Witkowski]

Have you tried this: SELECT ... WHERE (parameter 1) AND (parameter 2) AND (parameter 3) ... To speed up this, you need build an index on the fields you want to filter by.

See my article about Windows 7 Taskbar timer here on CodeProject

[Mycroft Holmes]

I rarely use intersect, so this may not be the best advice but. The above would result in 3 select statements against the same data structure, the results would then be compared and the intersection returned. As Dmitry suggested stick it in a where clause. You can almost certainly define the set of allowed parameters so some thing like this will do the trick:

ALTER PROC [DocumentsForEntity]
--DECLARE
@FleetID int,
@RoomID INT,
@ShipID INT,
@AutomationID int,
@CostumeID INT,
@ShowID INT
AS
--SET @FleetID = 1
SELECT
*
FROM dbo.vwDocument
CROSS JOIN dbo.Settings s

WHERE s.SettingCode = 'DocumentURL'
AND (ISNULL(@FleetID,0) = 0 OR FleetID = @FleetID)
AND (ISNULL(@RoomID,0) = 0 or RoomID = @Roomid)
AND (ISNULL(@ShipID,0) = 0 or ShipID = @ShipID)
AND (ISNULL(@AutomationID,0) = 0 or AutomationID = @AutomationID)
AND (ISNULL(@CostumeID,0) = 0 or CostumeID = @CostumeID)
AND (ISNULL(@ShowID,0) = 0 or ShowID = @ShowID)

ORDER BY DocumentType,DocName

The user can then select 1 or more of the parameters.

Never underestimate the power of human stupidity RAH

[James Shao]

Thank you guys, I've considered the WHERE clause, but each filter criteria already looks very complex (with 3 to 4 nested Select, subquery statements, a lot of nested WHERE statements too); so I thought using WHERE would make it too complicated for SQL to handle.