Adding "From Query" parameters degrades performance

Hi list,
This is the second time I've posted this question, the second time I've
created a report with "From Query" parameters and the second time I've had
problems with performance with the "From Query" parameters...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
Here's the question I posed before, unfortunately I've recieved no
response.. Can someone help, has anyone else noticed the same problem...?
The problem I'm having is very easy to reproduce...
On the main menu in report designer... In the Report->Report Parameters
property page there are two types of
parameters Non-Queried and From Query. Non-Queried appears to prompt the
user for its value while the From Query parameter appears to be populated
from the query as it's name implies by assigning a column from the dataset.
I've created several of these type of parameters because I need a way to
have a page header with data from the query repeat on every page since I
cannot put Fields in the page header and using a table with the
repeatonnewpage doesn't seem to work this was my only viable solution. If
you look at my posts for the last couple days you'll get an idea of what I'm
trying to do. So my question remains why would creating From Query
parameters slow the execution of my report...? Am I using them incorrectly
is there another direction I can take to to achieve my goal...
Thanks,
DanI use this all the time without any performance problem whatsoever. A few
questions for you. How many parameters do you have in the report? How many
rows are being returned? How long does it take for each of them to run from
outside of RS (from Query Analyzer).
Am I right in assuming the time taken is for the report to come up and
prompt for the parameters? It is not when you try to then view the report is
it?
Try a report that does nothing except have the report parameters based on
the queries. This would help pinpoint if it is the parameters or something
else.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>|||Hi Bruce,
Thanks for your reply, in this particular report I have 47 "From Query"
parameters which is less than the previous report I built which seemed to
take longer. For the report I'm working on now I have a header and a
subreport, the header returns one row of data and contains the from query
parameters. Strangely enough the troubles I was having with the header
report have seemed to clear up, although the first couple of times I tried
to run the report the report previewer indicated that my report had been
canceled and then I would click the View Report button again and the report
would run. My first report returned several rows of data but the data was
repeated for every line of detail this is the one that I'm continually
having performance problems with I'm assigning the repeated data to the From
Query parameters.
Dan
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> I use this all the time without any performance problem whatsoever. A few
> questions for you. How many parameters do you have in the report? How many
> rows are being returned? How long does it take for each of them to run
from
> outside of RS (from Query Analyzer).
> Am I right in assuming the time taken is for the report to come up and
> prompt for the parameters? It is not when you try to then view the report
is
> it?
> Try a report that does nothing except have the report parameters based on
> the queries. This would help pinpoint if it is the parameters or something
> else.
> Bruce L-C
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > Hi list,
> >
> > This is the second time I've posted this question, the second time I've
> > created a report with "From Query" parameters and the second time I've
had
> > problems with performance with the "From Query" parameters...
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> >
> > Here's the question I posed before, unfortunately I've recieved no
> > response.. Can someone help, has anyone else noticed the same
problem...?
> > The problem I'm having is very easy to reproduce...
> >
> > On the main menu in report designer... In the Report->Report Parameters
> > property page there are two types of
> > parameters Non-Queried and From Query. Non-Queried appears to prompt the
> > user for its value while the From Query parameter appears to be
populated
> > from the query as it's name implies by assigning a column from the
> dataset.
> > I've created several of these type of parameters because I need a way to
> > have a page header with data from the query repeat on every page since I
> > cannot put Fields in the page header and using a table with the
> > repeatonnewpage doesn't seem to work this was my only viable solution.
If
> > you look at my posts for the last couple days you'll get an idea of what
> I'm
> > trying to do. So my question remains why would creating From Query
> > parameters slow the execution of my report...? Am I using them
incorrectly
> > is there another direction I can take to to achieve my goal...
> >
> > Thanks,
> >
> > Dan
> >
> >
> >
>|||One other issue to look at as well is whether the performance issue is seen
in only the development environment versus both development and after
deployment. That would be one other issue to consider.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:uxjpJzrjEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> Thanks for your reply, in this particular report I have 47 "From Query"
> parameters which is less than the previous report I built which seemed to
> take longer. For the report I'm working on now I have a header and a
> subreport, the header returns one row of data and contains the from query
> parameters. Strangely enough the troubles I was having with the header
> report have seemed to clear up, although the first couple of times I tried
> to run the report the report previewer indicated that my report had been
> canceled and then I would click the View Report button again and the
report
> would run. My first report returned several rows of data but the data was
> repeated for every line of detail this is the one that I'm continually
> having performance problems with I'm assigning the repeated data to the
From
> Query parameters.
> Dan
>
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > I use this all the time without any performance problem whatsoever. A
few
> > questions for you. How many parameters do you have in the report? How
many
> > rows are being returned? How long does it take for each of them to run
> from
> > outside of RS (from Query Analyzer).
> >
> > Am I right in assuming the time taken is for the report to come up and
> > prompt for the parameters? It is not when you try to then view the
report
> is
> > it?
> >
> > Try a report that does nothing except have the report parameters based
on
> > the queries. This would help pinpoint if it is the parameters or
something
> > else.
> >
> > Bruce L-C
> >
> >
> > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
> > in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > > Hi list,
> > >
> > > This is the second time I've posted this question, the second time
I've
> > > created a report with "From Query" parameters and the second time I've
> had
> > > problems with performance with the "From Query" parameters...
> > >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> > >
> > > Here's the question I posed before, unfortunately I've recieved no
> > > response.. Can someone help, has anyone else noticed the same
> problem...?
> > > The problem I'm having is very easy to reproduce...
> > >
> > > On the main menu in report designer... In the Report->Report
Parameters
> > > property page there are two types of
> > > parameters Non-Queried and From Query. Non-Queried appears to prompt
the
> > > user for its value while the From Query parameter appears to be
> populated
> > > from the query as it's name implies by assigning a column from the
> > dataset.
> > > I've created several of these type of parameters because I need a way
to
> > > have a page header with data from the query repeat on every page since
I
> > > cannot put Fields in the page header and using a table with the
> > > repeatonnewpage doesn't seem to work this was my only viable solution.
> If
> > > you look at my posts for the last couple days you'll get an idea of
what
> > I'm
> > > trying to do. So my question remains why would creating From Query
> > > parameters slow the execution of my report...? Am I using them
> incorrectly
> > > is there another direction I can take to to achieve my goal...
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> > >
> >
> >
>|||I think the entire query is first run without parameters. Then from the
results of the query, the parameters are retrieved making them available for
selection. That would be why you would see a performance issue, especially
with larger sets of data.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>

add memory?

Hi,
I'm checking my performance of sql 2000 ent. on a w2k adv. server. There
are 2gb's of memory available. the server is dedicated for sql server
(at least i think it is).
I've noticed that the counter sql server: cache manager - cache hit
ratio is on average 67-70. The counter sql server:buffer manager -
buffer cache hit ratio is always above 99%. SQL Server uses dynamic mem
alloc.
All other import counters like processor and physical disk shows low values.
Could there be a memory bottleneck where the asigned cache for sql is
too low?You donâ't have a memory issue.
From SQL Server Books Online (BOL):
â'SQL Server: Cache Manager Object
The Cache Manager object provides counters to monitor how Microsoft® SQL
Serverâ?¢ uses memory to store objects such as stored procedures, ad hoc and
prepared Transact-SQL statements, and triggers.â'
Any SP, Trigger or Transact-SQL statements which are being called for the
1st time will not have an Execution Plan kept in the Cache. Besides, there
are many scenarios when SQL Server decides to generate a fresh Execution
Plan.
Have a look at the â'Execution Plan Caching and Reuseâ' topic in the BOL.
"Jason" wrote:
> Hi,
> I'm checking my performance of sql 2000 ent. on a w2k adv. server. There
> are 2gb's of memory available. the server is dedicated for sql server
> (at least i think it is).
> I've noticed that the counter sql server: cache manager - cache hit
> ratio is on average 67-70. The counter sql server:buffer manager -
> buffer cache hit ratio is always above 99%. SQL Server uses dynamic mem
> alloc.
> All other import counters like processor and physical disk shows low values.
> Could there be a memory bottleneck where the asigned cache for sql is
> too low?
>

add memory?

Hi,
I'm checking my performance of sql 2000 ent. on a w2k adv. server. There
are 2gb's of memory available. the server is dedicated for sql server
(at least i think it is).
I've noticed that the counter sql server: cache manager - cache hit
ratio is on average 67-70. The counter sql server:buffer manager -
buffer cache hit ratio is always above 99%. SQL Server uses dynamic mem
alloc.
All other import counters like processor and physical disk shows low values.
Could there be a memory bottleneck where the asigned cache for sql is
too low?You don’t have a memory issue.
From SQL Server Books Online (BOL):
“SQL Server: Cache Manager Object
The Cache Manager object provides counters to monitor how Microsoft? SQL
Server? uses memory to store objects such as stored procedures, ad hoc and
prepared Transact-SQL statements, and triggers.”
Any SP, Trigger or Transact-SQL statements which are being called for the
1st time will not have an Execution Plan kept in the Cache. Besides, there
are many scenarios when SQL Server decides to generate a fresh Execution
Plan.
Have a look at the ‘Execution Plan Caching and Reuse’ topic in the BOL.
"Jason" wrote:

> Hi,
> I'm checking my performance of sql 2000 ent. on a w2k adv. server. There
> are 2gb's of memory available. the server is dedicated for sql server
> (at least i think it is).
> I've noticed that the counter sql server: cache manager - cache hit
> ratio is on average 67-70. The counter sql server:buffer manager -
> buffer cache hit ratio is always above 99%. SQL Server uses dynamic mem
> alloc.
> All other import counters like processor and physical disk shows low value
s.
> Could there be a memory bottleneck where the asigned cache for sql is
> too low?
>

Add a table constraint and it's performance

I have a table with 100 gigs of data - and it seems it takes forever to add a
new constraint to this table, I finally have to cancel the statement after 1
hr of running. Is there a reason why or I am missing something.
below is my script
ALTER table [dbo].[acc_image]
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) < 2)
Label is the varchar of 50 and the fn_count_acc_image is to check the
duplicate label.
Thanks in advance.
It's quite normal. When you create a new check constraint all records in a
table are validated against this new constraint.
If you are sure that there are no records that violate this new constraint
(you can check this by writing the proper SELECT statement) use WITH NOCHECK
option:
ALTER table [dbo].[acc_image]
WITH NOCHECK
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
2)
Regards
Pawel Potasinski
Uytkownik "PhilN" <philngu@.msn.com> napisa w wiadomoci
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>
|||Hi
Read this article
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
"PhilN" <philngu@.msn.com> wrote in message
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>
|||Hi Uri,
The article is great, but if your primary goal is to prevent waiting at the
moment you use WITH NOCHECK anyway.
Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
Regards
Pawel Potasinski
Uytkownik "Uri Dimant" <urid@.iscar.co.il> napisa w wiadomoci
news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> Hi
> Read this article
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
>
>
> "PhilN" <philngu@.msn.com> wrote in message
> news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>
|||yes, this is exactly what I want to do - since I know 100% all rows in the
table are abide within this constraint. Using "with check" is not very
efficient in my case.
Beside with the table size that i have, the alter table took a whole night
(> 8 hr) to run and it lock up anyone who try to access the table. I have to
aborted the operation in the morning.
Thanks very much Pawel and everyone who made the suggestions.
"Pawel Potasinski" wrote:

> Hi Uri,
> The article is great, but if your primary goal is to prevent waiting at the
> moment you use WITH NOCHECK anyway.
> Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa3 w wiadomo?ci
> news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
>
>

Add a table constraint and it's performance

I have a table with 100 gigs of data - and it seems it takes forever to add
a
new constraint to this table, I finally have to cancel the statement after 1
hr of running. Is there a reason why or I am missing something.
below is my script
ALTER table [dbo].[acc_image]
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image
_id) < 2)
Label is the varchar of 50 and the fn_count_acc_image is to check the
duplicate label.
Thanks in advance.It's quite normal. When you create a new check constraint all records in a
table are validated against this new constraint.
If you are sure that there are no records that violate this new constraint
(you can check this by writing the proper SELECT statement) use WITH NOCHECK
option:
ALTER table [dbo].[acc_image]
WITH NOCHECK
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image
_id) <
2)
Regards
Pawel Potasinski
Uytkownik "PhilN" <philngu@.msn.com> napisa w wiadomoci
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_ima
ge_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi
Read this article
http://sqlblog.com/blogs/hugo_korne.../>
raints.aspx
"PhilN" <philngu@.msn.com> wrote in message
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_ima
ge_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi Uri,
The article is great, but if your primary goal is to prevent waiting at the
moment you use WITH NOCHECK anyway.
Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
Regards
Pawel Potasinski
Uytkownik "Uri Dimant" <urid@.iscar.co.il> napisa w wiadomoci
news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> Hi
> Read this article
> http://sqlblog.com/blogs/hugo_korne...
straints.aspx
>
>
> "PhilN" <philngu@.msn.com> wrote in message
> news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>|||yes, this is exactly what I want to do - since I know 100% all rows in the
table are abide within this constraint. Using "with check" is not very
efficient in my case.
Beside with the table size that i have, the alter table took a whole night
(> 8 hr) to run and it lock up anyone who try to access the table. I have to
aborted the operation in the morning.
Thanks very much Pawel and everyone who made the suggestions.
"Pawel Potasinski" wrote:

> Hi Uri,
> The article is great, but if your primary goal is to prevent waiting at th
e
> moment you use WITH NOCHECK anyway.
> Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window
.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa3 w wiadomo?ci
> news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
>
>

Add a table constraint and it's performance

I have a table with 100 gigs of data - and it seems it takes forever to add a
new constraint to this table, I finally have to cancel the statement after 1
hr of running. Is there a reason why or I am missing something.
below is my script
ALTER table [dbo].[acc_image]
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) < 2)
Label is the varchar of 50 and the fn_count_acc_image is to check the
duplicate label.
Thanks in advance.It's quite normal. When you create a new check constraint all records in a
table are validated against this new constraint.
If you are sure that there are no records that violate this new constraint
(you can check this by writing the proper SELECT statement) use WITH NOCHECK
option:
ALTER table [dbo].[acc_image]
WITH NOCHECK
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
2)
--
Regards
Pawel Potasinski
U¿ytkownik "PhilN" <philngu@.msn.com> napisa³ w wiadomo¶ci
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi
Read this article
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
"PhilN" <philngu@.msn.com> wrote in message
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi Uri,
The article is great, but if your primary goal is to prevent waiting at the
moment you use WITH NOCHECK anyway.
Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
--
Regards
Pawel Potasinski
U¿ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa³ w wiadomo¶ci
news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> Hi
> Read this article
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
>
>
> "PhilN" <philngu@.msn.com> wrote in message
> news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>>I have a table with 100 gigs of data - and it seems it takes forever to
>>add a
>> new constraint to this table, I finally have to cancel the statement
>> after 1
>> hr of running. Is there a reason why or I am missing something.
>> below is my script
>> ALTER table [dbo].[acc_image]
>> add constraint [chk_acc_image_table]
>> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id)
>> < 2)
>> Label is the varchar of 50 and the fn_count_acc_image is to check the
>> duplicate label.
>> Thanks in advance.
>|||yes, this is exactly what I want to do - since I know 100% all rows in the
table are abide within this constraint. Using "with check" is not very
efficient in my case.
Beside with the table size that i have, the alter table took a whole night
(> 8 hr) to run and it lock up anyone who try to access the table. I have to
aborted the operation in the morning.
Thanks very much Pawel and everyone who made the suggestions.
"Pawel Potasinski" wrote:
> Hi Uri,
> The article is great, but if your primary goal is to prevent waiting at the
> moment you use WITH NOCHECK anyway.
> Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa³ w wiadomo¶ci
> news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> > Hi
> > Read this article
> > http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
> >
> >
> >
> >
> >
> > "PhilN" <philngu@.msn.com> wrote in message
> > news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
> >>I have a table with 100 gigs of data - and it seems it takes forever to
> >>add a
> >> new constraint to this table, I finally have to cancel the statement
> >> after 1
> >> hr of running. Is there a reason why or I am missing something.
> >> below is my script
> >>
> >> ALTER table [dbo].[acc_image]
> >> add constraint [chk_acc_image_table]
> >> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id)
> >> < 2)
> >>
> >> Label is the varchar of 50 and the fn_count_acc_image is to check the
> >> duplicate label.
> >> Thanks in advance.
> >>
> >
> >
>
>

Add a new index in to my table

I wanted to inprove my database performance to add a new index in my table.
I just wanted to know are there any impact and should I add index without
any activities on the database?
Thanks millions for the informaiton,You can add an index when people are using the system, but be aware that the
entire table will be read to create the index, so they may see a performance
impact...
If you CAN wait until the evening, you probably should. but it is not the
end of the world.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:99EEC771-1D7C-45EE-A086-70C199A7DA2E@.microsoft.com...
>I wanted to inprove my database performance to add a new index in my table.
> I just wanted to know are there any impact and should I add index without
> any activities on the database?
> Thanks millions for the informaiton,