Reasonable to assemble FullName W/ A UDF

I break somone's name in a table in fields as First, Last, Middle,
Suffix.

Is it reasonable to use a UDF to create the full name as a string if I
need it, by passing it the First, Last, Middle, & Suffix fields?

Is this really slow to do? It's just sometimes I want to do a GroupBy
Query and I want to include the person's name in the Select of the
query and I don't know if the group by would work if I returned each
column seperately.

Any opinions on this?

Thanks.Using the function might be useful for consistency. I have worked in
databases where we have identical code all over the place: e.g.
LastName + ', ' + FirstName + ' ' + MiddleName.

I function would be nice for that. As far as speed goes, It may have a
negative effect.

Another option is to create a view of your data that has a FullName
column based on the others.|||Actually a computed columns works really nice and is probably easier to
use than a UDF. That way you can change the logic anytime you want and
don't have to change any application code.

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name = 'DisplayName'
))
begin
alter table Users add DisplayName as FirstName + ' ' + LastName + ' ('
+ Company + ')'
end

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name =
'SimpleDisplayName'
))
begin
alter table Users add SimpleDisplayName as FirstName + ' ' + LastName
end

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name =
'ReverseSimpleDisplayName'
))
begin
alter table Users add ReverseSimpleDisplayName as LastName + ', ' +
FirstName
end

Reasonable size of rdl file?

I'm working with a report that will produce 5 printed pages with key data
for a company. Each page will contain from 3 to 12 diagrams, there will be
39 diagrams totally in the rdl file. Plus some text boxes etc.
Each diagram has a dataset, plus some support datasets. Totally about 45
datasets (all using stored procedures).
I currently have 15 diagrams, and the rdl file is 140kB.
Am I stretching it? Experiences?
More detailed information:
The reason for keeping it in one rdl file is that a scheduled data driven
subscription will produce one PDF file per department (about 100
departments). The PDF file will be send to a printing shop which then will
mail the papers to each department. Each PDF file has 5 A3 pages with the
diagrams.
Keeping it in one RDL file allow for me to have one subscription.
Also, I have text boxes with values for colors for diagram bars etc (I pick
the values for the diagrams from the text boxes using an expression). When
people will start fiddling with changing colors, I can do it in one place,
instead of setting it in all 39 diagrams.
(I want to keep the work in Report Designer as much as possible. Even if I
can edit the RDL file, I don't want that the customer need to do this then
the project is rolled out and I'm out of here... :-). )
TIA
Tibor Karaszi
SQL Server MVPUndo/Redo in Report Designer is somewhat memory intensive. You may find that
you need to restart Report Designer if you use this feature extensively
during a single editing session. Another aspect is that very large RDLs,
especially those that contain a large number of images, can impact the
Report Designer's editing experience. In general, Report Designer should be
able handle this report.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> I'm working with a report that will produce 5 printed pages with key data
> for a company. Each page will contain from 3 to 12 diagrams, there will be
> 39 diagrams totally in the rdl file. Plus some text boxes etc.
> Each diagram has a dataset, plus some support datasets. Totally about 45
> datasets (all using stored procedures).
> I currently have 15 diagrams, and the rdl file is 140kB.
> Am I stretching it? Experiences?
>
> More detailed information:
> The reason for keeping it in one rdl file is that a scheduled data driven
> subscription will produce one PDF file per department (about 100
> departments). The PDF file will be send to a printing shop which then will
> mail the papers to each department. Each PDF file has 5 A3 pages with the
> diagrams.
> Keeping it in one RDL file allow for me to have one subscription.
> Also, I have text boxes with values for colors for diagram bars etc (I
pick
> the values for the diagrams from the text boxes using an expression). When
> people will start fiddling with changing colors, I can do it in one place,
> instead of setting it in all 39 diagrams.
> (I want to keep the work in Report Designer as much as possible. Even if I
> can edit the RDL file, I don't want that the customer need to do this then
> the project is rolled out and I'm out of here... :-). )
> TIA
> Tibor Karaszi
> SQL Server MVP
>|||OK, sounds good. Good tips. I don't use much UNDO/REDO in the first place, at least not at this
stage. :-)
One thing I do is to copy a whole measurement (three diagrams plus a few text boxes) and then start
work on the new sets of diagrams. This has worked fine so far, and I understand if RD becomes a bit
sluggish. So far I haven't noticed any problems.
I'd just hate to be on the last sets of diagram and I'm going beyond some boundary and RD or RS
comes crashing on me. Even with frequent backups it will take a little while to recover from such,
and if necessary I would then prefer doing it right from the beginning.
Thanks Bruce!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:eCj97r0mEHA.596@.TK2MSFTNGP11.phx.gbl...
> Undo/Redo in Report Designer is somewhat memory intensive. You may find that
> you need to restart Report Designer if you use this feature extensively
> during a single editing session. Another aspect is that very large RDLs,
> especially those that contain a large number of images, can impact the
> Report Designer's editing experience. In general, Report Designer should be
> able handle this report.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
>> I'm working with a report that will produce 5 printed pages with key data
>> for a company. Each page will contain from 3 to 12 diagrams, there will be
>> 39 diagrams totally in the rdl file. Plus some text boxes etc.
>> Each diagram has a dataset, plus some support datasets. Totally about 45
>> datasets (all using stored procedures).
>> I currently have 15 diagrams, and the rdl file is 140kB.
>> Am I stretching it? Experiences?
>>
>> More detailed information:
>> The reason for keeping it in one rdl file is that a scheduled data driven
>> subscription will produce one PDF file per department (about 100
>> departments). The PDF file will be send to a printing shop which then will
>> mail the papers to each department. Each PDF file has 5 A3 pages with the
>> diagrams.
>> Keeping it in one RDL file allow for me to have one subscription.
>> Also, I have text boxes with values for colors for diagram bars etc (I
> pick
>> the values for the diagrams from the text boxes using an expression). When
>> people will start fiddling with changing colors, I can do it in one place,
>> instead of setting it in all 39 diagrams.
>> (I want to keep the work in Report Designer as much as possible. Even if I
>> can edit the RDL file, I don't want that the customer need to do this then
>> the project is rolled out and I'm out of here... :-). )
>> TIA
>> Tibor Karaszi
>> SQL Server MVP
>>
>|||140 KB is certainly reasonable.
BTW: IIS 6.0 has a security restriction of a default 4 MB file
upload/download limit. This is due to a buffering restriction implemented in
IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore, if
RDL files get larger than 4 MB, you might run into an issue when uploading
them on the report server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> I'm working with a report that will produce 5 printed pages with key data
> for a company. Each page will contain from 3 to 12 diagrams, there will be
> 39 diagrams totally in the rdl file. Plus some text boxes etc.
> Each diagram has a dataset, plus some support datasets. Totally about 45
> datasets (all using stored procedures).
> I currently have 15 diagrams, and the rdl file is 140kB.
> Am I stretching it? Experiences?
>
> More detailed information:
> The reason for keeping it in one rdl file is that a scheduled data driven
> subscription will produce one PDF file per department (about 100
> departments). The PDF file will be send to a printing shop which then will
> mail the papers to each department. Each PDF file has 5 A3 pages with the
> diagrams.
> Keeping it in one RDL file allow for me to have one subscription.
> Also, I have text boxes with values for colors for diagram bars etc (I
pick
> the values for the diagrams from the text boxes using an expression). When
> people will start fiddling with changing colors, I can do it in one place,
> instead of setting it in all 39 diagrams.
> (I want to keep the work in Report Designer as much as possible. Even if I
> can edit the RDL file, I don't want that the customer need to do this then
> the project is rolled out and I'm out of here... :-). )
> TIA
> Tibor Karaszi
> SQL Server MVP
>|||Assuming the size of the file is roughly proportional to what I have now, I will probably end up
with a 0.5 MB file after adding the rest of the diagrams and some fluff. Even with lots of fluff, I
will most probably not go over 0.6-0.7 MB. Seems I have some margin, then.
And thanks for the tip about IIS size restriction, good to know if I happen to run into this at some
point. Not something I'd find easily myself. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u8LLCp1mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> 140 KB is certainly reasonable.
> BTW: IIS 6.0 has a security restriction of a default 4 MB file
> upload/download limit. This is due to a buffering restriction implemented in
> IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore, if
> RDL files get larger than 4 MB, you might run into an issue when uploading
> them on the report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
>> I'm working with a report that will produce 5 printed pages with key data
>> for a company. Each page will contain from 3 to 12 diagrams, there will be
>> 39 diagrams totally in the rdl file. Plus some text boxes etc.
>> Each diagram has a dataset, plus some support datasets. Totally about 45
>> datasets (all using stored procedures).
>> I currently have 15 diagrams, and the rdl file is 140kB.
>> Am I stretching it? Experiences?
>>
>> More detailed information:
>> The reason for keeping it in one rdl file is that a scheduled data driven
>> subscription will produce one PDF file per department (about 100
>> departments). The PDF file will be send to a printing shop which then will
>> mail the papers to each department. Each PDF file has 5 A3 pages with the
>> diagrams.
>> Keeping it in one RDL file allow for me to have one subscription.
>> Also, I have text boxes with values for colors for diagram bars etc (I
> pick
>> the values for the diagrams from the text boxes using an expression). When
>> people will start fiddling with changing colors, I can do it in one place,
>> instead of setting it in all 39 diagrams.
>> (I want to keep the work in Report Designer as much as possible. Even if I
>> can edit the RDL file, I don't want that the customer need to do this then
>> the project is rolled out and I'm out of here... :-). )
>> TIA
>> Tibor Karaszi
>> SQL Server MVP
>>
>|||Just as an FYI:
The rdl file ended up in size 330kB. RD was sluggish but worked. Howeverm
the data-driven subscription did not work. I don't have exact error message
here, but the service logged error messages about memory allocation. The
service terminated. I suspect this was in the PDF rendering (which worked
OK, but was slow both in VS and RM).
So I splitted the report up in 5 files (one per printed page) and it work
fine now. The job finished (for 37 PDF files) in about 5-7 minutes. So, I
now have 5 such jobs, one for each printed file.
Tibor
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u8LLCp1mEHA.2076@.TK2MSFTNGP15.phx.gbl...
> 140 KB is certainly reasonable.
> BTW: IIS 6.0 has a security restriction of a default 4 MB file
> upload/download limit. This is due to a buffering restriction implemented
in
> IIS 6.0 (AspMaxRequestEntityAllowed setting in MetaBase.xml). Therefore,
if
> RDL files get larger than 4 MB, you might run into an issue when uploading
> them on the report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:eDPik7ymEHA.3944@.TK2MSFTNGP10.phx.gbl...
> > I'm working with a report that will produce 5 printed pages with key
data
> > for a company. Each page will contain from 3 to 12 diagrams, there will
be
> > 39 diagrams totally in the rdl file. Plus some text boxes etc.
> > Each diagram has a dataset, plus some support datasets. Totally about 45
> > datasets (all using stored procedures).
> >
> > I currently have 15 diagrams, and the rdl file is 140kB.
> >
> > Am I stretching it? Experiences?
> >
> >
> > More detailed information:
> > The reason for keeping it in one rdl file is that a scheduled data
driven
> > subscription will produce one PDF file per department (about 100
> > departments). The PDF file will be send to a printing shop which then
will
> > mail the papers to each department. Each PDF file has 5 A3 pages with
the
> > diagrams.
> >
> > Keeping it in one RDL file allow for me to have one subscription.
> >
> > Also, I have text boxes with values for colors for diagram bars etc (I
> pick
> > the values for the diagrams from the text boxes using an expression).
When
> > people will start fiddling with changing colors, I can do it in one
place,
> > instead of setting it in all 39 diagrams.
> >
> > (I want to keep the work in Report Designer as much as possible. Even if
I
> > can edit the RDL file, I don't want that the customer need to do this
then
> > the project is rolled out and I'm out of here... :-). )
> >
> > TIA
> > Tibor Karaszi
> > SQL Server MVP
> >
> >
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
Eric
Eric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>
|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:

> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> certain
> INDEXDEFRAG
> problem
> good
> DTS
> different
> comparing
> hit
>
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certai
n
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite differen
t
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE fo
r
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:

> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> certain
> INDEXDEFRAG
> problem
> good
> DTS
> different
> comparing
> hit
>
>

Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:
> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> > Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
> certain
> > query works against some datasets within a couple tables, but mysteriously
> > never finishes against other datasets, perhaps some recently loaded ones.
> > It's mainly just one big table with some smaller auxiliary tables (used in
> > this query).
> >
> > I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
> INDEXDEFRAG
> > without effect. However, if I do a DTS "Copy objects and data between SQL
> > Server databases" of the offending database to a fresh database, the
> problem
> > goes away (without touching indexes or anything).
> >
> > As a further test, I restored a backup of the offending database onto a
> > different server, tried the query and it never returned, did the DTS copy
> > objects, queried again and it's fine. Past experience has shown that this
> > fresh, fixed database works fine for a while but can re-offend after a
> good
> > bit more data are loaded. It won't always be feasible to try this silly
> DTS
> > trick. The query plans for the query in the two databases are quite
> different
> > but I don't know enough to discern what the problem might be from
> comparing
> > them.
> >
> > I've searched around but cannot see a similar issue, but maybe I haven't
> hit
> > upon the right search keywords.
> >
> > Has anyone heard of or encountered such a problem? or other suggestions?
> >
> > thanks,
> > Eric
> >
>
>

Reasonable alternative to Dynamic SQL?

I have a web form that contains 7 search boxes. Any 2 can be used to search,
but only 2 out of the 7.
Since I dont know which ones will be used for search criteria, I figure
using Dynamic SQL will be the best solution and add WHERE clauses as needed.
However, the query is somewhat intense, and I worry about execution time.
Is there an alternative to this that I dont know about?SQL Server MVP Erland Sommarskog has written an excellent article on dynamic
searches and one of his conclusions is that dynamic SQL can be the right
choice from a performance poitn of view in some circumstances. you can find
his article here:
http://www.algonet.se/~sommar/dyn-search.html
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Learning SQL Server" <new@.bie.com> wrote in message
news:OHvm6aQVDHA.2200@.TK2MSFTNGP09.phx.gbl...
> I have a web form that contains 7 search boxes. Any 2 can be used to
search,
> but only 2 out of the 7.
> Since I dont know which ones will be used for search criteria, I figure
> using Dynamic SQL will be the best solution and add WHERE clauses as
needed.
> However, the query is somewhat intense, and I worry about execution time.
> Is there an alternative to this that I dont know about?
>
>|||Jacco:
Excellent is an understatement. Thank you for this great resource.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:O0PRU0QVDHA.2068@.TK2MSFTNGP11.phx.gbl...
> SQL Server MVP Erland Sommarskog has written an excellent article on
dynamic
> searches and one of his conclusions is that dynamic SQL can be the right
> choice from a performance poitn of view in some circumstances. you can
find
> his article here:
> http://www.algonet.se/~sommar/dyn-search.html
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Learning SQL Server" <new@.bie.com> wrote in message
> news:OHvm6aQVDHA.2200@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a web form that contains 7 search boxes. Any 2 can be used to
> search,
> > but only 2 out of the 7.
> >
> > Since I dont know which ones will be used for search criteria, I figure
> > using Dynamic SQL will be the best solution and add WHERE clauses as
> needed.
> > However, the query is somewhat intense, and I worry about execution
time.
> >
> > Is there an alternative to this that I dont know about?
> >
> >
> >
>