Dynamic query with SQL statement longer than 8000 chars

I have a stored procedure generating a dynamic SQL that runs against Analysi
s
Services. The statement can be quite complicated and can be longer than 8000
.
I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how man
y
@.strSQL variables I'll need, I was wondering if anybody has a better solutio
n
for this.
If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
that resembles a procedure in VB? I don't think UDFs would work here.
Thanks in advance for any help,
Carmen.Carmen (Carmen@.discussions.microsoft.com) writes:
> I have a stored procedure generating a dynamic SQL that runs against
> Analysis Services. The statement can be quite complicated and can be
> longer than 8000. I know I can use "exec (@.strSQL1 + @.strSQL2)" but
> since I don't know how many @.strSQL variables I'll need, I was wondering
> if anybody has a better solution for this.
Upgrade to SQL 2005, where you can use varchar(MAX) and you are free
of all hassle.

> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
If it's that bad, it may be better to build the query that has better
string capabilities, for instance VB.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Which parts of your SQL statement are dynamic? Is it possible that some of
your dynamic SQL could be done another way? Perhaps using a temp table in
place of a list for an in clause, or splitting parts of the dynamic SQL off
into views? I can only guess at what you are doing but I have to believe
that if you are getting over 16000 characters in your SQL that there must be
ways to simplify it and make the final SQL more concise.
Sometimes we get so concerned with making the current process work as
originally designed, and we overlook that there are flaws in the design.
Once we redesign the process to remove those flaws we find out that what we
thought was a problem was really only a symptom of another problem. This
may or may not be the case here, but it is worth considering nonetheless.
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:A7D9B446-D779-4A8B-8823-5701137660C3@.microsoft.com...
> I have a stored procedure generating a dynamic SQL that runs against
Analysis
> Services. The statement can be quite complicated and can be longer than
8000.
> I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how
many
> @.strSQL variables I'll need, I was wondering if anybody has a better
solution
> for this.
> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
> Thanks in advance for any help,
> Carmen.sql

Dynamic lookup with periodical (Yearly) data using JOIN

Hi,

I am generating a dynamic SQL query to perform a lookup based on criterias entered by the user. The quey is generated by a C# application and the target database is Sql Server 2005.

Basically I have 2 tables:

--Cie-- (Over 40k companies, 1 rows per companies, total 40k rows)
(
CieId int not null,
Symbol varchar,
CieName varchar
) Primary key(CieId)

--YearlyData-- (Over 40k companies, 20 years of data per companies, total 800k rows)
(
CieId int not null,
Year int not null,
Revenue currency,
NetIncome currency,
[...over 170 columns]
)Primary key(CieId, Year)

//Sample Case:
The user want to get the list of all the compagnies (CieId) where
YearlyData.NetIncome > 25k for YearlyData.Year 2005
and YearlyData.Revenue > 100k for YearlyData.Year 2005
and YearlyData.Revenue > 95k for YearlyData.Year 2004
and YearlyData.Revenue > 90k for YearlyData.Year 2003
and YearlyData.Revenue of YearlyData.Year 2005 > YearlyData.Revenue of YearlyData.Year 2004

Currently, the Sql query that would be generated looks like this:

SELECT Cie.CieId
FROM Cie
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2005) Y2005 ON Y2005.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2004) Y2004 ON Y2004.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2003) Y2003 ON Y2003.CieId = Cie.CieId
WHERE 1=1
AND Y2005.NetIncome > 25000
AND Y2005.Revenue > 100000
AND Y2004.Revenue > 95000
AND Y2003.Revenue > 90000
AND Y2005.Revenue > Y2004.Revenue

I dont feel good about all those JOINs especially since there is up to 20 years of data and the user could use all of them in his query. The request become slower with each new join.

Does anyone see a better way to do this, maybe a PIVOT or something?

Thanks

Math:

I mocked this up establishing 40k cie entries and 800k yearly records. When I ran your query I got the expected execution plan and the expected performance. When the data is not cached it ran in about 15 seconds and with the data cached in ran in zero seconds. I got, of course, 3 table (clustered index) scans as it read in the yearly data. I got the merge join (which I expected).

My view is that (1) this is a report and (2) it doesn't figure to be run often and therefore performance for this is not critical. Now, I did show about 10,000 logical IOs as it scanned the tables. This will roughly amount to a grab of about 80Meg of buffer cache whenever this query runs. For most modern servers used for SQL Server 2005 this should not be a problem. Since (1) this is a report and doesn't figure to run often and (2) it seems to provide very adequate performance for a report I would not take any time to try to upgrade the performance of this; I don't figure you will get a good return on time invested in this report unless you are having some specific problems.

One thing that can be done to provide a marginal performance boost for this particular is to build a "cover index" for the specific columns you are using for this report; however, I don't feel like this is a good investment either. The modest boost received from the cover index does not figure to be a good tradeoff for the cost of the necessary cover index.

Are you having some specific problems? If so, what are they?

Dave

|||

Dave, thank you for the reply.

I dont have any specific problems other than perfomances, I am just trying to see if there is a better way to construct this kind of query (i.e. without 1 join per year). This is not really a report, it is more like a screener, a tools used when looking for companies to invest in. The screener is used frequently so performance is an issue. The data is readonly with only 1 update at the end of each day so we indexed every columns (grouped logicaly).

The query in the OP was only a sample case, the user can really compare:
"columnX of yearX" with "columnX of yearY"
"columnX of yearX" with "columnY of yearY"
"columnX of yearX" with "columnY of yearX"
"columnX of yearX" with a static value (like 250.95)
and more

Do you think the query can be resructured so it perform better (like removing the "join per year")?

Thanks

|||Would partitioning by year be helpful? It seems to be a common filter.|||

You can do below query instead and there is no reason to join with Cie table if you want just CieId values and there is FK relationship between YearlyData & Cie on CieId column.

select y.CieId

from YearlyData as y

where y.Year in (2003, 2004, 2005)

group by y.CieId

having max(case y.Year when 2005 then NetIncome end) > 25000

and max(case y.Year when 2005 then Revenue end) > 100000

and max(case y.Year when 2004 then Reveue end) > 95000

and max(case y.Year when 2003 then Reveue end) > 90000

and max(case y.Year when 2005 then Revenue end) > max(case y.Year when 2004 then Reveue end)

This query will provide best performance if there is an index on Year or (Year, CieId). You seem to have primary key on (CieId, Year) which will not help for this particular query resulting in a table/index scan.

|||

Math:

Both ideas help. When I ran Umachandar's query it reduced the logical IOs from about 10000 to about 3200, reduced alpha read time to about 4600 ms and reduced beta read time to around 420 ms. When I then partitioned the data on year logical IO was further reduced from 3200 to about 500, alpha read time from 4600ms to about 3200ms and beta read time from 420ms to about 380 ms.

Dave

|||

Both idea looks good, I will see if this query structure is compatible with all my test case and keep you inform.

Thank you for your help.

Dynamic If Update() in Trigger - Urgent!

Hi All

I have a question about generating dynmamicly If Update() statement in a
trigger..

in My db, there is a table that holds some column names of an another table.
for example;

Columns Table-A: Col1, Col2, Col3, Col4,Col5

Table-B: Col2, Col5 (The selected columns of Table A)

Then, in the Trigger of Table-A I use;

Select name from syscolumns where id=object_id('Table-A')

fetch next from TableA_Cursor into @.strColName

then, I used a statement like this..

if UPDATE(' + @.strColName + ')

But it gives "incorrect syntax" error..

How can I write this line?

Thanks alot in advance...

--
Message posted via http://www.sqlmonster.comI don't believe it is possible to use IF UPDATE() dynamically, nor is
it necessary. It also doesn't really make much sense to reference IF
UPDATE() in a cursor since the result will be the same for every row.
Anyway you shouldn't use cursors in triggers - they just turn your
set-based update statements into row-based updates, which is bad in
principle from a design poiunt of view and generally performs very
poorly.

If you want your triggers to take account of table structure changes
then generate the trigger code dynamically at DESIGN time rather than
runtime.

If you need a trigger to act on what data has changed then join the
Inserted and Deleted virtual tables and compare the columns. IF UPDATE
doesn't tell you what changed, only which columns were referenced by
the update statement.

If you need more help, please post a fuller description of your problem
including DDL and sample data.

--
David Portas
SQL Server MVP
--

Dynamic generating a SQL statement that includes "INTO #temp"?

Hi there. I started with a simple stored procedure like
select col1, col2 into #temp from table1
select * from #temp
...Do something else here...
For some reason, I'd like to generate the first query dynamically.
declare @.sqlStmt varchar(200)
set @.sqlStmt = 'select col1, col2 into #temp from table1'
exec (@.sqlStmt)
select * from #temp
Unfortunately, it seems like #temp table is not visible anymore in the
"select * from #temp"
- Is it because exec creates a new session, and so #temp created in
the new session is not visible in the original session?
- Using global temp table ##temp may be too dangerous if two
concurrent users are running the same stored procedure. Right?
- Another workaround is to include the "select * from #temp" into the
@.sqlStmt. However, it is not a very elegant solution if the size of
stored procedure is big, but the portion that I'd like to generate
dynamically is small.
- Any other better solution?
ThanksInstead of using the SELECT...INTO statement, create a fixed temporary table
or a table variable and insert data into it. I can't really see any
legitimate reasons to use SELECT...INTO in your case (based on your post).
ML
http://milambda.blogspot.com/|||Thanks! That helps!|||>> Any other better solution? <<
First, avoid dynamic SQL. This says that you have no idea what you
wanted the procedure to do, so you have to "fake it" at the last
minute.
Next, from your narative it looks like a derived table or a VIEW is a
better answer, instead of mimicking the way we wrote scratch tapes in
1950's tape file systems.
CREATE VIEW Foobar (col1, col2)
AS
SELECT col1, col2 FROM Table1;
and then in the stored procedure, which will be compiled in the
database and not built dynamically. The VIEW will always be current.
CREATE PROCEDURE Woowoo (..)
BEGIN ..
SELECT col1, col2 FROM Foobar;
.Do something else here..
END;|||--CELKO-- (jcelko212@.earthlink.net) writes:
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
Rubbish. To see an example of how dynamic SQL can be used to solve a
common business problem, see http://www.sommarskog.se/dyn-search.html.
(And in difference to your books, this is a free resource. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To answer you questions:
When you exec (@.SQL), exec sp_executesql, or exec MyStoredProcedure, any
local temporary tables (#MyTable) will go out of scope and be automatically
deleted when the respective statement ends.
You can get around this by using a global temporary table (##MyTable) but as
you stated, a global temporary table is visable to all processes. Not only
does the same stored procedure executed concurrently have problems but ANY
T-SQL located in other stored procedure, dynamically submiteed by
applications, etc. that happen to create/use a global temporary table of the
same name (##MyTable) will have problems too.
SQL Server protects local temporary tables by concatenating a system
generated numeric suffix to the name. You could do the same. Seeing you're
dynamically creating the global table, you could concatenate the @.@.SPID
(process id) or something else. But then the remaining code would have to b
e
dynamic too and either exec (@.SQL) or exec master.dbo.sp_executesql (which
has more functionality)
Sounds like you need a permanent user table in your database (as suggested
by another forum member) and have a column that contains your @.@.SPID to
idenify the block of rows that belong to your process as opposed to other
concurrent executions.
Col1 - PK, int, identity, clustered (data is always added at the end of the
table data)
Col2 - SPID
Col3-n logical keys of your data
unique constraint on col2, col3, etc.
for rerunability, delete from xxx where spid = @.@.SPID so you have a fresh
workspace
if you need several workspaces, add another column after SPID that creates a
sub-block of data. you're sp can have as many sub-blocks as needed.
Just my two cents,
Joe
"domtam@.hotmail.com" wrote:

> Hi there. I started with a simple stored procedure like
> select col1, col2 into #temp from table1
> select * from #temp
> ....Do something else here...
> For some reason, I'd like to generate the first query dynamically.
> declare @.sqlStmt varchar(200)
> set @.sqlStmt = 'select col1, col2 into #temp from table1'
> exec (@.sqlStmt)
> select * from #temp
> Unfortunately, it seems like #temp table is not visible anymore in the
> "select * from #temp"
> - Is it because exec creates a new session, and so #temp created in
> the new session is not visible in the original session?
> - Using global temp table ##temp may be too dangerous if two
> concurrent users are running the same stored procedure. Right?
> - Another workaround is to include the "select * from #temp" into the
> @.sqlStmt. However, it is not a very elegant solution if the size of
> stored procedure is big, but the portion that I'd like to generate
> dynamically is small.
> - Any other better solution?
> Thanks
>|||> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
First, ignore Celko.
Because he sticks to the ANSI SQL standard he probably hasn't used these
Microsoft SQL Server features so he just blanks them and says they are
kludges etc...
Celko needs to get out and do some real development work and get some real
development experience.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134003996.366060.171490@.z14g2000cwz.googlegroups.com...
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
> Next, from your narative it looks like a derived table or a VIEW is a
> better answer, instead of mimicking the way we wrote scratch tapes in
> 1950's tape file systems.
> CREATE VIEW Foobar (col1, col2)
> AS
> SELECT col1, col2 FROM Table1;
> and then in the stored procedure, which will be compiled in the
> database and not built dynamically. The VIEW will always be current.
> CREATE PROCEDURE Woowoo (..)
> BEGIN ..
> SELECT col1, col2 FROM Foobar;
> ..Do something else here..
> END;
>

Dynamic Dataset to Reports

Hi,
I have some selection criteria in my .aspx web page and based on the
selections, by using an stored procedure, I am generating the dataset. As of
now, I am displaying this in HTML table format.
Now I have designed the report using Reporting Services and have deployed it
onto the Report Server. I would like to know how do I pass this dataset from
my .aspx webpage onto the report and generate the same.
Could any one please let me know the links to materials explaining this
technique or any help would be highly appreciated.
Regards,
Sudhakara.T.P.Hi,
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link your dataset to a report at runtime by using ReportViewer.LocalReport
methods or at disign level too.
Hopefully I help you.
Arturo Carrión
at Teimpo Hard Development team
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> Hi,
> I have some selection criteria in my .aspx web page and based on the
> selections, by using an stored procedure, I am generating the dataset. As
> of
> now, I am displaying this in HTML table format.
> Now I have designed the report using Reporting Services and have deployed
> it
> onto the Report Server. I would like to know how do I pass this dataset
> from
> my .aspx webpage onto the report and generate the same.
> Could any one please let me know the links to materials explaining this
> technique or any help would be highly appreciated.
> Regards,
> Sudhakara.T.P.|||Hi,
Thank you very much for your reply.
Infact, I tried doing this earlier, but the problem with this is that, in
the preview mode, I am not getting the print button to print the button, even
tough I have set the showprintoption to true.
Any alternative solution for this?
Regards,
Sudhakara.T.P.
"tiempotecnologia@.newsgroup.nospam" wrote:
> Hi,
> Try to do this.
> 1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
> 2. Configure your ReportViewer to a LocalReport mode. This will allow you to
> link your dataset to a report at runtime by using ReportViewer.LocalReport
> methods or at disign level too.
> Hopefully I help you.
> Arturo Carrión
> at Teimpo Hard Development team
> "Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> escribió en el
> mensaje news:3188FD41-0A7F-448F-AE2C-7AD2423F343F@.microsoft.com...
> > Hi,
> > I have some selection criteria in my .aspx web page and based on the
> > selections, by using an stored procedure, I am generating the dataset. As
> > of
> > now, I am displaying this in HTML table format.
> > Now I have designed the report using Reporting Services and have deployed
> > it
> > onto the Report Server. I would like to know how do I pass this dataset
> > from
> > my .aspx webpage onto the report and generate the same.
> > Could any one please let me know the links to materials explaining this
> > technique or any help would be highly appreciated.
> > Regards,
> > Sudhakara.T.P.
>
>