Partitioned ...Functions and Stored Procedures? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Partitioned ...Functions and Stored Procedures?

Author

Topic

grrr223
Starting Member

30 Posts

Posted - 2004-06-21 : 14:48:57

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


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.


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.

armanbalin
Starting Member

3 Posts

Posted - 2004-06-22 : 02:36:17

2 curious questions, what made you decide to store your data across several databases? Do you foresee any problems in using just one database?

Pardon me for asking these questions but I personally believe that using one database per app could make your programming life much, much easier as long as you name your tables cleverly.

grrr223
Starting Member

30 Posts

Posted - 2004-06-22 : 09:56:23

Unfortunately I don't have a choice. It's our accounting software. Each database uses a different cuurrency (U.S, Canadian, Australian), and actually from that standpoint it makes a lot of sense. All transactions within a single database, it doesn't matter what the units are, they're all the same. This way you only need to woryr about exchange rates during the few transactions between companies.

This is how we have 3 separate databases. I would almost like to have the queries reside in each database, because then they would each be identical (and much easier to maintain), as opposed to 3 copies of each query each pointing at one of the databases in my 4th database. Then I could just point my access data project at the database the user selected.

Actually, the way I have it now works quite well....except when the queries get complicated enough that the query engine starts looking at all 3 databases. But that is only on a few complicated joins. The rest of my queries, which are still multiple-joins, it works well. There is the slight overhead of these "partitioned functions" but the query engine is smart enough to ignore them most of the time.

I am slowly working on a solution, TABLE-VALUED USER DEFINED FUNCTIONS. All of my "partitioned functions" were in-line functions which only allow a single select statement, but table-valued functions allow logic and multiple select statements, so I can use the IF logic to keep it from looking at the other tables better.

Thank you all for your help.

Kristen
Test

22859 Posts

Posted - 2004-06-22 : 10:45:52

As I understand it, for a horizontal partition, you need to have a VIEW which UNION ALLs the various tables together AND have a column representing the "primary key" which is implimented in the individual tables with a CHECK CONSTRAINT.


CREATE VIEW PartitionedView
AS
SELECT	MyCompany,
	... other columns ...
FROM	DATA_05.dbo.OEORDHDR_SQL
UNION ALL
SELECT	MyCompany,
	... other columns ...
FROM	DATA_06.dbo.OEORDHDR_SQL
UNION ALL
SELECT	MyCompany,
	... other columns ...
FROM	DATA_09.dbo.OEORDHDR_SQL

and then, in each table, you need to have a constraint on "MyCompany" - so, to illustrate, something along the lines of:


CREATE TABLE DATA_05.dbo.OEORDHDR_SQL
(
	MyCompany varchar(10) PRIMARY KEY
		CHECK (MyCompany = 'Data_05')
	... rest of defintion ...
)

repeat for other tables!

SQL is then smart enough to use the CHECK CONSTRAINT in each table to completly ignore the unused, and mutually exclusive, tables in the partition view - and thus give you the performance you are after.

Hopefully you've got a unique value column (currency, by the sounds of it) in each table that you can hijack.

Kristen

grrr223
Starting Member

30 Posts

Posted - 2004-06-22 : 11:48:04

Unfortunately, I can not modify the structure of any of the tables in our accounting software to add the contstraints. Man how I would love for that option to work, I got so excited when I first read about partitioned views. The other really cool thing about partitioned views is that more often than not, they are also updatable! (although this is not a requirement at the moment).

Well, thank you 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!!!! (KINDA, in some situations it is slower, but for a few of the queries I needed it for, it is MUCH faster , i.e. almost 2 minutes down to 10 seconds faster!)

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


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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources