cas1
asked on
Dynamic SQL in Function UDF
Hi,
following problem, SQL Server 2005:
I have a user definded function (needs to be a function because i use it within a query) that returns a value such like this:
My problem now is that the name of the table (here: myTable) depends on the variable @Project_ID that is passed to the function (for instance if @Project_ID=5 the table name would be somothing like myTable_5)
I know that dynamic SQL is not working withing UDF's but has anyone an idea for a workaround?
Temporary tables are no solution because the table handles huge amount of records
Thanks in advance
Andy
following problem, SQL Server 2005:
I have a user definded function (needs to be a function because i use it within a query) that returns a value such like this:
CREATE FUNCTION [dbo].[fct_prg_GetCostsPerProject](@Project_ID int)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result=ISNULL(SUM(Costs),0)
FROM dbo.myTable
WHERE Mx_Project_fID = @Project_ID
return @Result
END
My problem now is that the name of the table (here: myTable) depends on the variable @Project_ID that is passed to the function (for instance if @Project_ID=5 the table name would be somothing like myTable_5)
I know that dynamic SQL is not working withing UDF's but has anyone an idea for a workaround?
Temporary tables are no solution because the table handles huge amount of records
Thanks in advance
Andy
ASKER
Hi, angellll,
unfortunately this is impossible to manage due to the table names are not limited to a certain numbers
Andy
unfortunately this is impossible to manage due to the table names are not limited to a certain numbers
Andy
the only thing I see is that you build up a view on all the relevant tables, along with the field that you would map the projectid value to.
is the structure of those table(s) the same? in which case, why not have 1 single table (eventually partitioned) ...
anyhow, the view method would be like this:
and your function:
is the structure of those table(s) the same? in which case, why not have 1 single table (eventually partitioned) ...
anyhow, the view method would be like this:
CREATE VIEW dbo.MyView
AS
SELECT 5 ProjectID, Costs
FROM dbo.myTable5
UNION ALL
SELECT 6 ProjectID, Costs
FROM dbo.myTable6
and your function:
CREATE FUNCTION [dbo].[fct_prg_GetCostsPerProject](@Project_ID int)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result=ISNULL(SUM(Costs),0)
FROM dbo.myView
WHERE ProjectID = @Project_ID
return @Result
END
ASKER
My problem is: at this time that I have one table with all values in, using the @Project_ID to separate beteween the different projects. Since there are > 17.000.000 recordsets in the table (lots of projects, each project up to 800.000 recordsets) i have heavy performance issues. The table holds plenty of columns that all needs to be evaluated in different evaluations. I tried around with setting the right indexes using the execution plan of the queries and the index recommondations, but I would need to place over 15 indexes to solve the performance issuse when evaluating the table. But the disadvantage is that all operations with the table itself become very very slow (due to the number of indexes). So deleting and recreating the 800.000 records of a single project take much to long.
So my approach was to have a seperate table for each project. That would solve my performance issues.
Maybe you have a solution for this ???
Andy
So my approach was to have a seperate table for each project. That would solve my performance issues.
Maybe you have a solution for this ???
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are using SQL Server 2005 or greater. I think partitioning your table is the best option.
It is very simple to setup and there are lot of information in the web.
It is very simple to setup and there are lot of information in the web.
hi
another solution that is very easy to implement is "Filtered Indexes"
if you have 2008 version.
a index that will hold only data requierd for you in queries by filter.
another solution that is very easy to implement is "Filtered Indexes"
if you have 2008 version.
a index that will hold only data requierd for you in queries by filter.
ASKER
Setting the clustered index works great and prevent me from a database redesign
Thanks a lot
Thanks a lot
but that is very far from "dynamic" and "manageable"
sorry, but there is no solution for this.