Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Help with a Partitioned...Function?

    I am using an Access 2003 ADP with a SQL Server 2000 backend.

    My accounting data is stored in 3 identically structured databases on the same server (data_05, data_06, data_09), and all of my ADP's functions and stored procedures are stored in a 4th database called Data_00. I can only make changes to Data_00, I can not affect the tables in the other 3 databases.

    I want my users to be able to select the database (company) from a combo box, and from there, the interface is identical regardless of what data they're working with.

    To facilitate this, without having to write 3 copies of every query (I started out that way, it's NOT fun) or using much dynamic SQL, I have done this:

    For all the tables that I access regularly in the 3 databases, I have created a "partitioned function" in data_00. for example, for my Order Header table, I have created the below function

    Code:
    ALTER FUNCTION dbo.fnMC_OEORDHDR
    	(@Company char(7))
    RETURNS TABLE
    AS
    RETURN( 
    	SELECT	*
    	FROM	DATA_05.dbo.OEORDHDR_SQL
    	WHERE	'Data_05' = @Company
    
    	UNION ALL
    
    	SELECT	*
    	FROM	DATA_06.dbo.OEORDHDR_SQL
    	WHERE	'DATA_06' = @Company
    
    	UNION ALL
    
    	SELECT	*
    	FROM	DATA_09.dbo.OEORDHDR_SQL
    	WHERE	'DATA_09' = @Company
    	)
    It actually works quite well for simple queries against it. I just pass it the @company I want, and the SQL Server Query Engine is smart enough to only look at the table in the database I want.

    Unfortunately, for more complicated queries, it still tries to look at ALL the tables which is VERY bad and slow.

    In the few cases where I can use stored procedures (lookup tables for example), I use something similar to the below. It accomplishes the same as the above function, but it does a much better job. The unfortunate part is that you can't use IF statements in functions.

    Unfortunately, I need to use functions for these "partitioned queries" because I want to use them in place of the original table names, and you can't do that with a stored procedure.

    Code:
    ALTER PROCEDURE dbo.spMC_OEORDHDR
    	(@Company char(7))
    AS 
    	IF @Company = 'Data_05'
    		SELECT     *
    		FROM		dbo.Payments_Data_05
    	If @Company = 'Data_06'
    		SELECT 	*
    		FROM		dbo.Payments_Data_06
    	If @Company = 'Data_09'
    		SELECT 	*
    		FROM 	dbo.Payments_Data_09
    Does anyone have any suggestions on how to create a "partitioned function" that does a better job of tricking the query engine to only look at the table I want

    Any help would be greatly appreciated. Thank you.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ummmm... why are you doing this server side, do you have some strict requirements you have to meet?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    Keep in mind, I can not make any changes to the individual Data_05, 06, 09 databases, only my database Data_00.

    Well...here are my choices, right?:

    1. For each report that I want to create, create 3 queries, one with the FROM clause pointing at each of my 3 data companies. Choose which query to use on the client side. As in:
    dbo.fnOEORDHDR_Data_05
    dbo.fnOEORDHDR_Data_06
    dbo.fnOEORDHDR_Data_09

    OR

    2. Create 1 query for each report that allows me to dynamicly choose which of the 3 companies to point at. (As in my above example). The easiest way to do this is to use my Multi-company "partitioned functions". This way, I just pass the functions the company I need. As in:
    dbo.fnOEORDHDR(@Company)

    OR

    3. Do everything on the client side using Dynamic SQL.

    I REALLY want to avoid using dynamic SQL as much as possible for performance and security reasons. (At the moment, it's very easy to control access to my application, I just restrict the user from runnning the SP that returns the recordset for that report).

    The other reason that I want to do as much on the server as possible is that I want to return as few records as possible, some of the tables have 100,000 records, which gets even bigger when doing joins, so again, I'd like to keep it all on the server.

    Maybe my examples aren't making sense because they're so simple. What I want to do (And actually, what I am successfully doing) is create multiple-join stored procedures based on these "partitioned functions" to use as the record-sources for my reports. The only problem is that after a certain point, the SQL Server Query Engine starts trying to look at the tables in all 3 databases, which takes forever. I think what I am really looking for, is just a possible better way of writing my UNION function , maybe moving around some of the clauses or something, that will keep the Query Engine from only looking at the table in the company that I have passed as a parameter to the function

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well ok, I really think you should do it client side but that's just me.

    If you JUST HAVE TO do it this way, perhaps you could do something like this:

    ALTER PROCEDURE dbo.spMC_OEORDHDR
    (@Company CHAR(7))
    AS
    EXECUTE ('SELECT * FROM dbo.' + @Company)

    There are also ways to take care of security concerns client side. I don't see how performance is really an issue there. Generally any processing done client side seems to enhance performance rather then the other way around. But, it's your choice.

    Last edited by Teddy; 06-21-04 at 17:19.

  5. #5
    Join Date
    Feb 2004
    Posts
    126
    Unfortunately, I need to use a function because I use these "partitioned functions" in the FROM clause in my stored procedures. For example, to pull information about customers and their orders, I would do something like this:

    1. Create a partitioned function for the Customer table
    Code:
    ALTER FUNCTION dbo.MC_ARCUSFIL
    
    (@Company char(7))
    
    RETURNS TABLE
    
    AS
    RETURN( 
    
    	SELECT	*
    	FROM	DATA_05.dbo.ARCUSFIL_SQL
    	WHERE	'Data_05' = @Company
    
    	UNION ALL
    
    	SELECT	*
    	FROM	DATA_06.dbo.ARCUSFIL_SQL
    	WHERE	'DATA_06' = @Company
    
    	UNION ALL
    
    	SELECT	*
    	FROM	DATA_09.dbo.ARCUSFIL_SQL
    	WHERE 	'DATA_09' = @Company
    	
    	)
    2. Create a partioned function for the orders table
    Code:
    ALTER FUNCTION dbo.MC_OEORDHDR
    
    	(@Company char(7))
    
    RETURNS TABLE
    
    AS
    
    RETURN( 
    	SELECT	*
    	FROM	DATA_05.dbo.OEORDHDR_SQL
    	WHERE	'Data_05' = @Company
    
    	UNION ALL
    
    	SELECT	*
    	FROM	DATA_06.dbo.OEORDHDR_SQL
    	WHERE	'DATA_06' = @Company
    
    	UNION ALL
    
    	SELECT	*
    	FROM	DATA_09.dbo.OEORDHDR_SQL
    	WHERE	'DATA_09' = @Company
    	
    	)
    3. Create a stored procedure to use as the recordsource for my report.

    Code:
    ALTER PROCEDURE dbo.CustomerOrders
    (@Company char(7))
    AS SELECT     *
    FROM         dbo.MC_ARCUSFIL(@Company) MC_ARCUSFIL INNER JOIN
                          dbo.MC_OEORDHDR(@Company) MC_OEORDHDR ON MC_ARCUSFIL.cus_no = MC_OEORDHDR.cus_no

    It sounds VERY silly to go through the first two steps just to create the stored procedure, but I only have create the partioned functions on maybe 10 tables, and then I reference them in stored procedures. The above stored procedure works beautifully. If I look at the execution plan, the query engine only looks at the table in teh company I told it to.

    However, when the stored procedure queries get more complicated, that's when the query engine starts looking at the table in all 3 databases.

    Until the reports started getting so complicated that my "partitioned functions" were looking in all the databases, it was actually a great way of getting around the annoying design concept of these 3 identical databases that I can't make any changes too. My application is still very much under development, and I don' want to have to make every single change in 3 places, I'd rather do it this way for now, until things get finalized.

    I greatly appreciate your help though.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I will admit that sp's aren't my strongsuit, but since you are using an sp to return the recordsource anyways, why not do everything in one sp?

    CREATE PROCEDURE spTest (@Company CHAR(7))
    AS

    EXECUTE ('SELECT * FROM ' + @Company + '.dbo.ARCUSFIL_SQL + INNER JOIN ' + @Company +
    '.dbo.OEORDHDR_SQL ON MC_ARCUSFIL.cus_no = MC_OEORDHDR.cus_no')
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Posts
    126
    I'll try that one I guess, I guess the performance hit of using the dynamic sql in the report's recordsource is going to be a lot less than the hit of the "partitioned functions" looking at the records in 3 databases instead of one.

    And since even though it's dynamic sql, it will still be in a stored procedure, I will be able to secure it properly.

    thanks for your help, I hadn't really thought of it that way, I kinda thought stored procedure OR dynamic sql (client-side).

  8. #8
    Join Date
    Feb 2004
    Posts
    126
    Actually...is there a way to use Dynamic SQL in a function? My queries are a bit complicated, and it is nice to be able to be able to build upon functions as the building blocks of my stored procedures?

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't believe so. IIRC, UDF's will not execute dynamic sql, nor accept non-deterministic functions.

    Kind of limited..

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Feb 2004
    Posts
    126
    Hmmm...that's no fun. In that case, I don't really know how to proceed at this point. I'm not looking for a way to write the stored procedures as much as I am looking for a way to write the functions better. Are there any tricks that would force the Query Engine to only look at one part of the Union function?

    I really need to be able to put these things into functions so that I can reference them as I would tables when creating more complicated queries.

    Thanks for your help. I think I am going to mess around with table-valued functions because they accept the IF logic that I am looking for, but for some reason, I believe I have been down this road before and it didn't work out as I had hoped .

    Wow, SQL sure is an adventure isnt it, there's a reason for everything, and limitations on everything as well. Just because it works one way (say, dynamic SQL in an SP) does not in any way imply that it will work another way (dynamic SQL in a function).

  11. #11
    Join Date
    Feb 2004
    Posts
    126
    Well, thank you Teddy for all of your help and forcing me to think WAY outside of the box.

    I tried using a table-valued function (which allows you to use multiple select statements and IF statements, as long as you return one record set)

    AND IT WORKED!!!!

    All that I had to do was change the one "partitioned function" from the UNION query that I had above to one similar to:

    Code:
    ALTER         FUNCTION dbo.MC_OEORDHDR_TV
    
    	(@prmCompany varchar(8))
    
    RETURNS @OEORDHDR TABLE(
    	...
          TABLE STRUCTURE GOES HERE
                 ...
    )
    AS
    
    BEGIN
    
    IF @prmCompany = 'Data_05'
    	
    	INSERT INTO @OEORDHDR
    	SELECT	*	
    	FROM	Data_05.dbo.OEORDHDR_SQL
    
    IF @prmCompany = 'Data_06'
    
    	INSERT INTO @OEORDHDR
    	SELECT		* 
    	FROM	Data_06.dbo.OEORDHDR_SQL
    
    IF @prmCompany = 'Data_09'
    
    	INSERT INTO @OEORDHDR
    	SELECT		* 
    	FROM	Data_09.dbo.OEORDHDR_SQL
    	
    RETURN
    Wow, at least my wasted day came to some fruition.

    Have a good night!

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    haha that's awesome!!

    I've been struggling with referencing dynamic columns and tables myself for the last day, you may actually be putting me on a lead too!!

    Good show sir!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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