Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    England
    Posts
    15

    Question Unanswered: complex queries: what approach is realistic?

    I am migrating a very complex query from Access to SQLServer2000, and realize that as a newbie, a series of connected or nested stored procs. isn't as easy as I'd hoped.

    The original macro query had 4 levels of sub-level queries, many of these queries use outer joins and grouping extensively, but looking at other posts, it seems that you can't have a select statement that directly references fields within an output recordset from an outer storedProc.

    Alternatives could be:

    a) use subqueries
    b) stored procs linking via cretaed temp tables
    c) nested stored procs
    d) linked stored procs
    e) views
    f) combinations of the above

    As a newbie to SQLServer2k, I'm a little bewildered as to what approach I should take. My 1st consideration is to get this query rebuilt and working, and then to refine/alter to maximise performance.

    My original and preferred approach was to use a series of nested SPs. Can anyone give me some guidance please?

    Alex

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    RE:What approach I should take?
    It depends on your query.

    RE: You can't have a select statement that directly references fields within an output recordset from an outer storedProc

    Configure your server as linked to itself

    select *
    from openquery(linkserver,'exec sp_help')

    select *
    from openquery(linkserver,'exec sp_help') where name='sysobjects'

    select *
    into #q
    from openquery(linkserver,'exec sp_help') where name='sysobjects'

    All queries work.

  3. #3
    Join Date
    Jan 2003
    Location
    England
    Posts
    15
    My original query (from MS Access) is:

    SELECT qPreferencesForSessionCombined.SessionID, qPreferencesForSessionCombined.CritID, qActiveProductValuesForNums.ProductNumValue, qCriteria.UnitsOfMeasure, qPreferencesForSessionCombined.PreferredValue, IIf([CritType]="num",[qActiveProductValuesForNums].[ProductID],[qPreferencesForSessionCombined].[ProductID]) AS ProductID, IIf([CritType]="num",100*([ProductNumValue]-[WorstValue])/([PreferredValue]-[WorstValue]),[ScoreForOption]) AS UnadjustedScore, IIf([UnadjustedScore]>100,100,[UnadjustedScore]) AS Score, [Score]*((([MaxPosition]+1)-[Position])*2)/([MaxPosition]*([MaxPosition]+1)) AS WeightedScore, IIf([CritType]="num",[qProducts].[ProductName],[qPreferencesForSessionCombined].[ProductName]) AS ProductName
    FROM (qCriteria INNER JOIN qPreferencesForSessionCombined ON qCriteria.CritID = qPreferencesForSessionCombined.CritID) LEFT JOIN (qActiveProductValuesForNums LEFT JOIN qProducts ON qActiveProductValuesForNums.ProductID = qProducts.ProductID) ON qPreferencesForSessionCombined.CritID = qActiveProductValuesForNums.CritID;

    In the above top-level query, the "q" prefix indicates a subordinate query. The query itself is part of a web-application that provides a scoring facility against user-entered preferences. Data is held within SQLServer2000, and the application server used is Coldfusion, front-ended by MX-Flash.

    In your reply (thanks, by the way), you introduce "linkserver" - which is new to me. How does this actually work? Also, in the light of the above query, can you suggest a suitable approach, please?

    Alex






    Originally posted by ispaleny
    RE:What approach I should take?
    It depends on your query.

    RE: You can't have a select statement that directly references fields within an output recordset from an outer storedProc

    Configure your server as linked to itself

    select *
    from openquery(linkserver,'exec sp_help')

    select *
    from openquery(linkserver,'exec sp_help') where name='sysobjects'

    select *
    into #q
    from openquery(linkserver,'exec sp_help') where name='sysobjects'

    All queries work.

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Linkserver is only alias to server created by SP sp_addlinkedserver or EM (Server/Security/Linked servers). It can be used to access any OLEDB datasource.

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    create procedure sp_Test as
    SELECT
    qPreferencesForSessionCombined.SessionID
    , qPreferencesForSessionCombined.CritID
    , qActiveProductValuesForNums.ProductNumValue
    , qCriteria.UnitsOfMeasure
    , qPreferencesForSessionCombined.PreferredValue
    , ProductID =case when [CritType]="num" then [qActiveProductValuesForNums].[ProductID] else [qPreferencesForSessionCombined].[ProductID] end
    , UnadjustedScore =case when [CritType]="num" then 100.0*([ProductNumValue]-[WorstValue])/([PreferredValue]-[WorstValue]) else [ScoreForOption] end
    , Score =case when [UnadjustedScore]>100 then 100 else [UnadjustedScore] end
    , WeightedScore=[Score]*((([MaxPosition]+1)-[Position])*2)/([MaxPosition]*([MaxPosition]+1))
    , ProductName =case when CritType]="num" then [qProducts].[ProductName] else [qPreferencesForSessionCombined].[ProductName] end
    FROM dbo.qCriteria qCriteria
    INNER JOIN dbo.qPreferencesForSessionCombined qPreferencesForSessionCombined ON qCriteria.CritID = qPreferencesForSessionCombined.CritID
    LEFT JOIN dbo.qActiveProductValuesForNums qActiveProductValuesForNums ON qPreferencesForSessionCombined.CritID = qActiveProductValuesForNums.CritID
    LEFT JOIN dbo.qProducts qProducts ON qActiveProductValuesForNums.ProductID = qProducts.ProductID
    GO
    EXEC sp_Test

  6. #6
    Join Date
    Jan 2003
    Location
    England
    Posts
    15
    Ispaleny: Thanks for your quick response.

    I'm trying your suggested SELECT statement. To do this, I have started to create the subordinate queries (eg. "qPreferencesForSessionCombined") as views. These work fine in that they can effectively nest the subordinate queries/views, but unfortunately cannot handle parameters!

    I've done some more digging in the Help files, and see that user defined functions can maybe be the answer.

    It would seem that I can't avoid the need to create temporary tables to hold result sets for the next higher query/view.

    As your solution suggests, I would like to encapsulate the whole "macro"query within one stored procedure that would also carry the necessary parameters to feed the relevant subordinate queries.

    Do you think that using UDFs is a good approach. If so, then how could you get user-supplied values into the parameters that would feed the UDF?

    Hoping you can still help, and thanks again for help so far.
    Regards
    Alex

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    There are 3 types of UDF table function

    Type 1. Inline function
    --------------------------------------------------------------------------------------------------------------
    A replacement of a parametrized derived table. Resultset is virtual,
    original table indexes are used outside function,only one query, created fast.
    Inline function without parameters can be replaced by view, which can be also faster (indexed).

    Code:
    GO
    create function ufn_Test_INL
    (
    @param varchar(20)
    ) returns table as return 
    select YourCol 
    from YourTab 
    where YourCol2=@param 
    GO
    Type 2. Unindexed function
    --------------------------------------------------------------------------------------------------------------
    Resultset is built in function, no indexes are used outside function,
    multiple queries,created fast.

    Code:
    GO
    create function ufn_Test_UIDX
    (
    @param varchar(20)
    ) returns @t table 
    (
    YourCol varchar(20)
    ) as begin
    insert @t(YourCol)
     select YourCol 
     from YourTab 
     where YourCol2=@param 
     return 
    end
    GO
    Type 3. Indexed function
    --------------------------------------------------------------------------------------------------------------
    Resultset is built in function, primary key of function is used in query, created slower.
    Time lost during creation of primary key can spare much more time in join of large table on primary key.

    Code:
    GO
    create function ufn_Test_IDX
    (
    @param varchar(20)
    ) returns @t table 
    (
    YourCol varchar(20), primary key clustered (YourCol)
    ) as begin
    insert @t(YourCol)
     select YourCol 
     from YourTab 
     where YourCol2=@param 
     return 
    end
    GO
    Sample exec, notice that owner prefix must be used.
    --------------------------------------------------------------------------------------------------------------
    Code:
    select * from dbo.ufn_Test_INL('test')
    select * from dbo.ufn_Test_UIDX('test')
    select * from dbo.ufn_Test_IDX('test')
    Functions are faster (not recompiled), when created with schemabinding option.
    You must use owner prexix for all tables referenced in query.

    Accelerated inline function

    Code:
    GO
    create function ufn_Test_INL
    (
    @param varchar(20)
    ) 
    returns table with schemabinding as return 
    select YourCol 
    from dbo.YourTab 
    where YourCol2=@param 
    GO

  8. #8
    Join Date
    Jan 2003
    Location
    England
    Posts
    15

    Use of UDFs/Stored Procs?

    Thanks once again, Isapaleny,

    I managed to solve my problems by using UDFs instead of Views which cannot accept any parameters. Essentially what I now have is a series of linked dependent UDFs (which generate their respective temp.tables), and then a stored procedure at the top level, which in turn communicates with my Coldfusion CFC (& then to Flash MX via Flash Remoting).

    Have a good weekend
    Regards, Alex

  9. #9
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Here to help Pacmbella.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •