A couple of easy questions

Dear all,
1)
I was wondering myself, how many characters can handle Sql2k when you define
a field? Such limitation is common for any object? I mean, tables, indexes,
jobs.
I've got an ASP application which allow the user create fields and I would
need to know that rule in order to define a maxlenght value.
2)
I had in my EM remote servers added but today they are dissapeared although
now are currently up. Does anyone ever experienced such behaviour? It coul
d
be a shutdown of that server or even if that server is off from the client
vanishes...
Thanks a lot for your suggestions,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)Hi
1)
Lookup in the BOL for maximum and minimum ovjects limits article
2)
I have not, but did you refresh your EM?
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
> Dear all,
> 1)
> I was wondering myself, how many characters can handle Sql2k when you
> define
> a field? Such limitation is common for any object? I mean, tables,
> indexes,
> jobs.
> I've got an ASP application which allow the user create fields and I would
> need to know that rule in order to define a maxlenght value.
> 2)
> I had in my EM remote servers added but today they are dissapeared
> although
> now are currently up. Does anyone ever experienced such behaviour? It
> could
> be a shutdown of that server or even if that server is off from the client
> vanishes...
>
> Thanks a lot for your suggestions,
>
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||hi again Uri,
Yes, I did. How odd, isn't?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> Hi
> 1)
> Lookup in the BOL for maximum and minimum ovjects limits article
> 2)
> I have not, but did you refresh your EM?
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
>
>|||
> Yes, I did. How odd, isn't?
Hi Enric, do you have 'Open object explorer and new query' in Tools/Options
dialog under Environment/General leaf?
I noticed that if object explorer is not mentioned in selected combo,
servers don't show up.
Peter|||Hi Rogas,
I don't see that option. From Entreprise Manager->Tools->Options I have two
leafs: General and Advanced. In General there is nothing related with you
said.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Rogas69" wrote:

>
> Hi Enric, do you have 'Open object explorer and new query' in Tools/Option
s
> dialog under Environment/General leaf?
> I noticed that if object explorer is not mentioned in selected combo,
> servers don't show up.
> Peter
>
>

5, 8 or 25 concurents workloads ?

Hello,
I'm usng MSDE2000A (not MSDE2000)
The MSDE2000 documentation speak about 5 concurents
operations limitation.
The "Approriate uses of MSDE Faq" speak about 25
concurents users (means concurents operations?)
http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
(see the "Can I use MSDE as a database for Web
applications?" question)
The SQL Server 2000 Workload Governor speak about 8
concurents operations
http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
Who's right? What's the exact number limitation for
MSDE2000A? 8 or 5 or 25 ?
Thanks for reply.
Georges
Thanks for posting this question...
I was myself confused after reading all this.. but after clarification from many posts out here, it seems that the workload governer kicks in after 5 concurrent operations (3 are reserved for the system, so total is 8)..
But the verdict of the people using MSDE is that it seems to work fairly well even if the number of concurrent operations goes till 25 and above.. you'll just have to test you system.
Hope it helps.
dev
"Georges" wrote:

> Hello,
> I'm usng MSDE2000A (not MSDE2000)
> The MSDE2000 documentation speak about 5 concurents
> operations limitation.
> The "Approriate uses of MSDE Faq" speak about 25
> concurents users (means concurents operations?)
> http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
> (see the "Can I use MSDE as a database for Web
> applications?" question)
> The SQL Server 2000 Workload Governor speak about 8
> concurents operations
> http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
> Who's right? What's the exact number limitation for
> MSDE2000A? 8 or 5 or 25 ?
> Thanks for reply.
> Georges
>
|||The governor is intended to kick in when there are 5 concurrent *USER*
workloads (a workload is NOT the same concept as a user/connection).
The 8 that you see referenced is because there are 3 workloads allotted for
system activity (designed so that the system can continue to do work even if
5 workloads are tying up the system).
The 25 that you see referenced is 25 concurrent users, not concurrent
workloads. Even with 25 users connected to the database, it is unlikely ,
unless you have really inefficient queries (in which case you probably won't
notice the governor anyway). As dev_kh stated, you'll have to test your
system to see if (a) the governor kicks in at all, and (b) whether its
effect is tolerable.
http://msdn.microsoft.com/library/en...r_sa2_0ciq.asp
All of this confusion is one of the primary reasons the governor was removed
in SQL Server 2005 Express, I think.
http://www.aspfaq.com/
(Reverse address to reply.)
"Georges" <anonymous@.discussions.microsoft.com> wrote in message
news:133701c46f12$14ae53c0$a501280a@.phx.gbl...
> Hello,
> I'm usng MSDE2000A (not MSDE2000)
> The MSDE2000 documentation speak about 5 concurents
> operations limitation.
> The "Approriate uses of MSDE Faq" speak about 25
> concurents users (means concurents operations?)
> http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
> (see the "Can I use MSDE as a database for Web
> applications?" question)
> The SQL Server 2000 Workload Governor speak about 8
> concurents operations
> http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp
> Who's right? What's the exact number limitation for
> MSDE2000A? 8 or 5 or 25 ?
> Thanks for reply.
> Georges
|||I thought that MSDE had a limit of 10 concurrent users? Is it 25?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23q8AjzybEHA.2660@.TK2MSFTNGP12.phx.gbl...
> The governor is intended to kick in when there are 5 concurrent *USER*
> workloads (a workload is NOT the same concept as a user/connection).
> The 8 that you see referenced is because there are 3 workloads allotted
for
> system activity (designed so that the system can continue to do work even
if
> 5 workloads are tying up the system).
> The 25 that you see referenced is 25 concurrent users, not concurrent
> workloads. Even with 25 users connected to the database, it is unlikely ,
> unless you have really inefficient queries (in which case you probably
won't
> notice the governor anyway). As dev_kh stated, you'll have to test your
> system to see if (a) the governor kicks in at all, and (b) whether its
> effect is tolerable.
> http://msdn.microsoft.com/library/en...r_sa2_0ciq.asp
> All of this confusion is one of the primary reasons the governor was
removed
> in SQL Server 2005 Express, I think.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Georges" <anonymous@.discussions.microsoft.com> wrote in message
> news:133701c46f12$14ae53c0$a501280a@.phx.gbl...
>
|||> I thought that MSDE had a limit of 10 concurrent users? Is it 25?
No, it is neither. As I tried to explain, MSDE doesn't have a limit on
concurrent users at all (not 5, not 8, not 10, not 25, not 5000). There is
a governor that throttles performance at or around 5 simultaneous workloads,
which is *NOT* the same as a connection.
The 25 was a *suggestion* on one of the product pages, for how many
concurrent users a typical system running MSDE could support. This could be
completely different from project to project, as hardware, application code,
usage, bandwidth, database design etc. can differ, and all of these things
impact how an application performs. So you will need to test it out to see
how many simultaneous users and/or workloads your specific scenario can
support.
To provide an analogy for the difference between a concurrent user and a
concurrent workload. Imagine yourself at the DMV, and there are 40 people
waiting around, not doing much. Two people are at the registration counter,
and one person is getting her picture taken for a new drivers' license. The
40 people are "concurrent users" and the other three are "concurrent
workloads" - since they're the only ones actually doing anything productive.
http://www.aspfaq.com/
(Reverse address to reply.)
|||ahhh - thanks!
love the DMV analogy, btw :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Bqzmw%23bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> No, it is neither. As I tried to explain, MSDE doesn't have a limit on
> concurrent users at all (not 5, not 8, not 10, not 25, not 5000). There
is
> a governor that throttles performance at or around 5 simultaneous
workloads,
> which is *NOT* the same as a connection.
> The 25 was a *suggestion* on one of the product pages, for how many
> concurrent users a typical system running MSDE could support. This could
be
> completely different from project to project, as hardware, application
code,
> usage, bandwidth, database design etc. can differ, and all of these things
> impact how an application performs. So you will need to test it out to
see
> how many simultaneous users and/or workloads your specific scenario can
> support.
> To provide an analogy for the difference between a concurrent user and a
> concurrent workload. Imagine yourself at the DMV, and there are 40 people
> waiting around, not doing much. Two people are at the registration
counter,
> and one person is getting her picture taken for a new drivers' license.
The
> 40 people are "concurrent users" and the other three are "concurrent
> workloads" - since they're the only ones actually doing anything
productive.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||> love the DMV analogy, btw :-)
Yeah, I was hoping you were in the US; most others wouldn't get it. ;-)
http://www.aspfaq.com/
(Reverse address to reply.)
|||> most others wouldn't get it. ;-)
Just to be clear, I don't mean the concept (I think it's pretty clear even
if you replace DMV with McDonald's or the tax office or your human resources
department).
I mean the irony of talking about the DMV in terms of performance and
workload governing. Most of the locations I have been to have an unlimited
concurrent user threshold, but the staff has a concurrent workload limit of
exactly 1. ;-)
A

48 Second Query Takes 40+ minutes To Display in Report Manager

I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
limitation, but here's the situation.
I have a report based on a single query that accepts 3 parameters:
1) Company (0/1)
2) Month (Date)
3) Retrieve either YTD or Monthly values (0/1)
Executing the query in Query Analyzer using parameters that would be the
worst case scenario from a processing stand point, it takes 48 seconds to
execute, and returns 2,054 rows (by 15 columns). Using parameters for the
best case scenario, it takes about 2-4 seconds, and returns 38 rows.
I can easily execute the best case scenario in SRS to display the report,
and it takes about 5-7 seconds to render as a web page. I continue to
time-out on the worst case scenario report, even after setting the timeout
value to 40 minutes in Site Settings in Report Manager.
Help! We've recommended the SRS architecture to the customer as a solution
over Crystal Reports. It now appears that large (even though it's only 2000
rows) result sets using 2+ groupings is causing SRS to seize in rendering the
report.
Any ideas or suggestions as to how to troubleshoot this?
Thanks!please post ddl, your query etc to give us an idea on how to replicate your
problem. you don't give us much to go on. returning 2,000 + rows is not an
issue in reporting services.
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||OK, thanks Mike. I'll include the RDL file, the script for the source tables,
and the query. If you offer any tips for trouble shooting this, that would be
great. I can't believe that a 48 second query takes 40 minutes with SRS.
Link To Download Here (very small file):
http://home.comcast.net/~wcsmith/KM/SRS.zip
Thanks!!!
"mike" wrote:
> please post ddl, your query etc to give us an idea on how to replicate your
> problem. you don't give us much to go on. returning 2,000 + rows is not an
> issue in reporting services.
> "Smit-Dog" wrote:
> > I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> > limitation, but here's the situation.
> >
> > I have a report based on a single query that accepts 3 parameters:
> >
> > 1) Company (0/1)
> > 2) Month (Date)
> > 3) Retrieve either YTD or Monthly values (0/1)
> >
> > Executing the query in Query Analyzer using parameters that would be the
> > worst case scenario from a processing stand point, it takes 48 seconds to
> > execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> > best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> >
> > I can easily execute the best case scenario in SRS to display the report,
> > and it takes about 5-7 seconds to render as a web page. I continue to
> > time-out on the worst case scenario report, even after setting the timeout
> > value to 40 minutes in Site Settings in Report Manager.
> >
> > Help! We've recommended the SRS architecture to the customer as a solution
> > over Crystal Reports. It now appears that large (even though it's only 2000
> > rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> > report.
> >
> > Any ideas or suggestions as to how to troubleshoot this?
> >
> > Thanks!|||Hi SmitDog,
Could you please try something? Hard code the values of the parameters in
your query. See if this improves the performance. If it does, please send
me a good repro and I will find out why this is happening. I tried to repro
in house but not been able to yet.
--
| Thread-Topic: 48 Second Query Takes 40+ minutes To Display in Report
Manager
| thread-index: AcUFWClju0gpbyR3SsGjzN0z7PcJMQ==| X-WBNR-Posting-Host: 68.61.30.192
| From: "=?Utf-8?B?U21pdC1Eb2c=?=" <SmitDog@.discussions.microsoft.com>
| Subject: 48 Second Query Takes 40+ minutes To Display in Report Manager
| Date: Fri, 28 Jan 2005 08:41:05 -0800
| Lines: 27
| Message-ID: <EABFFAF9-25FC-4F39-8EEB-DAF43A60C0F4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41210
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I don't know if I've quickly hit a SQL Reporting Services (SRS)
architecture
| limitation, but here's the situation.
|
| I have a report based on a single query that accepts 3 parameters:
|
| 1) Company (0/1)
| 2) Month (Date)
| 3) Retrieve either YTD or Monthly values (0/1)
|
| Executing the query in Query Analyzer using parameters that would be the
| worst case scenario from a processing stand point, it takes 48 seconds to
| execute, and returns 2,054 rows (by 15 columns). Using parameters for the
| best case scenario, it takes about 2-4 seconds, and returns 38 rows.
|
| I can easily execute the best case scenario in SRS to display the report,
| and it takes about 5-7 seconds to render as a web page. I continue to
| time-out on the worst case scenario report, even after setting the
timeout
| value to 40 minutes in Site Settings in Report Manager.
|
| Help! We've recommended the SRS architecture to the customer as a
solution
| over Crystal Reports. It now appears that large (even though it's only
2000
| rows) result sets using 2+ groupings is causing SRS to seize in rendering
the
| report.
|
| Any ideas or suggestions as to how to troubleshoot this?
|
| Thanks!
||||BTW, here is the query I'm using as a benchmark. You won't have the data, but
it basically takes 1-3 seconds when using Company = 1 (returning about 40
rows), and about 50 seconds when going against Company = 0 (returning about
2000 rows) in Query Analyzer.
Company = 1 in Report Manager renders in about 5-7 seconds. Company = 0
takes about 45 minutes. FWIW, this is on a 1.3GHz laptop with 512MB RAM,
using SQL Server 2000 (SP3).
Also, the big difference is that Company = 1 only has 59,000 records it's
going against, whereas Company=0 has 2,000,000 records it has to query
against.
exec sp_GetKeyJobSiteIndicatorsData 0, '2004-06-01', 0
"Smit-Dog" wrote:
> OK, thanks Mike. I'll include the RDL file, the script for the source tables,
> and the query. If you offer any tips for trouble shooting this, that would be
> great. I can't believe that a 48 second query takes 40 minutes with SRS.
> Link To Download Here (very small file):
> http://home.comcast.net/~wcsmith/KM/SRS.zip
> Thanks!!!
> "mike" wrote:
> > please post ddl, your query etc to give us an idea on how to replicate your
> > problem. you don't give us much to go on. returning 2,000 + rows is not an
> > issue in reporting services.
> >
> > "Smit-Dog" wrote:
> >
> > > I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> > > limitation, but here's the situation.
> > >
> > > I have a report based on a single query that accepts 3 parameters:
> > >
> > > 1) Company (0/1)
> > > 2) Month (Date)
> > > 3) Retrieve either YTD or Monthly values (0/1)
> > >
> > > Executing the query in Query Analyzer using parameters that would be the
> > > worst case scenario from a processing stand point, it takes 48 seconds to
> > > execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> > > best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> > >
> > > I can easily execute the best case scenario in SRS to display the report,
> > > and it takes about 5-7 seconds to render as a web page. I continue to
> > > time-out on the worst case scenario report, even after setting the timeout
> > > value to 40 minutes in Site Settings in Report Manager.
> > >
> > > Help! We've recommended the SRS architecture to the customer as a solution
> > > over Crystal Reports. It now appears that large (even though it's only 2000
> > > rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> > > report.
> > >
> > > Any ideas or suggestions as to how to troubleshoot this?
> > >
> > > Thanks!|||Hey Brad,
I posted a link to the basic files involved a couple of posts up. It's
missing the data, and that may be the deal-breaker in reproducing the
problem. I can supply a link to a DB backup if that would help, or perhaps
you can see something in my query, table structure, or RDL that would cause
this severe degradation in performance.
I'll try hardcoding the parameters and see if that helps.
""Brad Syputa - MS"" wrote:
> Hi SmitDog,
> Could you please try something? Hard code the values of the parameters in
> your query. See if this improves the performance. If it does, please send
> me a good repro and I will find out why this is happening. I tried to repro
> in house but not been able to yet.
> --
> | Thread-Topic: 48 Second Query Takes 40+ minutes To Display in Report
> Manager
> | thread-index: AcUFWClju0gpbyR3SsGjzN0z7PcJMQ==> | X-WBNR-Posting-Host: 68.61.30.192
> | From: "=?Utf-8?B?U21pdC1Eb2c=?=" <SmitDog@.discussions.microsoft.com>
> | Subject: 48 Second Query Takes 40+ minutes To Display in Report Manager
> | Date: Fri, 28 Jan 2005 08:41:05 -0800
> | Lines: 27
> | Message-ID: <EABFFAF9-25FC-4F39-8EEB-DAF43A60C0F4@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41210
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I don't know if I've quickly hit a SQL Reporting Services (SRS)
> architecture
> | limitation, but here's the situation.
> |
> | I have a report based on a single query that accepts 3 parameters:
> |
> | 1) Company (0/1)
> | 2) Month (Date)
> | 3) Retrieve either YTD or Monthly values (0/1)
> |
> | Executing the query in Query Analyzer using parameters that would be the
> | worst case scenario from a processing stand point, it takes 48 seconds to
> | execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> | best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> |
> | I can easily execute the best case scenario in SRS to display the report,
> | and it takes about 5-7 seconds to render as a web page. I continue to
> | time-out on the worst case scenario report, even after setting the
> timeout
> | value to 40 minutes in Site Settings in Report Manager.
> |
> | Help! We've recommended the SRS architecture to the customer as a
> solution
> | over Crystal Reports. It now appears that large (even though it's only
> 2000
> | rows) result sets using 2+ groupings is causing SRS to seize in rendering
> the
> | report.
> |
> | Any ideas or suggestions as to how to troubleshoot this?
> |
> | Thanks!
> |
>|||One other weird thing that may be related...
Every few days, when running the query in QA using parameters for a worst
case scenerio, it would take 30-35 mintues to run the query. For some reason
it went from under a minute to 30x longer. To find the bottleneck, I
commented out the grouping of CASE statements that would SUM values based on
the code, and used hardcoded values instead. This immediately took the query
(again, in QA) from 30 minutes back down to 20 seconds or so. I slowly went
back to the SP and began to add back the CASE statements one by one, trying
to determine which one was causing the bottleneck. As I added them back, the
query time jumped back up to 48 seconds, very acceptable and suddendly back
to where it was before. Even after adding back all CASE statements, the query
was back down to 48 seconds.
I have no idea as to why this "fixes" the query, and the resulting code is
exactly the same. Perhaps when it "recompiles" it, it's internal algorithm is
fixed? Sorry this is so weird, but it is outside my area of expertise.
FWIW, these are the CASE statements and hardcoded values I toggle between to
"fix" the query.
**************************
sum ( case when ( seg1_code >= 6100 and seg1_code <=6145 ) then Balance end)
as BalanceAdditionalLabor,
sum ( case when ( seg1_code >= 6310 and seg1_code <=6400 ) then Balance end)
as BalanceControllables,
sum ( case when ( seg1_code >= 6001 and seg1_code <= 6008) then Balance end)
as BalanceDirectLabor,
sum ( case when ( ( ( seg1_code >= 6701 and seg1_code <=6785 ) or seg1_code
in ( 6002, 8000, 8005 ) ) ) then Balance end) as BalanceFixed,
sum ( case when ( seg1_code =6201 ) then Balance end) as BalanceMaterials,
sum ( case when ( seg1_code = 6204 ) then Balance end) as BalanceRepairs,
sum ( case when ( seg1_code >= 5001 and seg1_code <= 5999 ) then Balance
end) as BalanceSales,
sum ( case when ( seg1_code =5002 ) then Balance end) as BalanceSpecial,
sum ( case when ( seg1_code = 5100 ) then Balance end) as BalanceSubContractor
--BalanceAdditionalLabor=1,
--BalanceControllables=1,
--BalanceDirectLabor=1,
--BalanceFixed=1,
--BalanceMaterials=1,
--BalanceRepairs=1,
--BalanceSales=1,
--BalanceSpecial=1,
--BalanceSubContractor=1
****************************
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||OK... I've done some more testing, and this may not be related to SRS, but
rather some weirdness going on with the query itself.
It appears that I need to comment out a set of CASE statements, substitute
them with hardcoded values, execute the query a few times, un-comment the
CASE statements, then the query takes 48 seconds to run instead of 45 minutes.
I have gone through this little exercise dozens of times, and it magically
"fixes" the query everytime. Again, no perceived performance problem when the
query is run against the smaller company (much smaller result set - 40
records), just when going against the company that returns a large record set
(2400 records).
It's almost as if the query processor gets tangled in a funk, and going
through the comment/un-commenting of the CASE statments allows it to
recompile cleanly/correctly.
Unless someone has a clue as to what would be causing this weirdness, or can
see something glaringly wrong with the query, I'm to the point where I
re-write the query to get rid of the case statements altogether.
Any ideas or suggestions?
Thanks!
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||... And I've found out that once I execute the query using Company = 1, then
go back and execute it using Company = 2, the stored procedure is "broke"
again, and takes 40+ minutes to execute. I have to edit the SP, comment out
the CASE statetments, execute it using hardcoded values instead, go back and
un-comment the CASE statements, then using Company = 0 takes 45 seconds.
What the heck is going on here?
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!

4 GB Size limitation

Hi,

Can somebody tell me if the 4 GB database size limit is per database or for all the databases

Regards

Harish

The limit is per database and its only for the data file not the log file.|||Thanks Euan.|||

Do you have any supporting documentation about this in the MS Site

Sorry to bother.

|||

Here is one place, comparing to MSDE

http://msdn2.microsoft.com/en-us/library/ms165672(SQL.90).aspx

Interesting blog...

http://blogs.msdn.com/czhower/archive/2006/01/06/510192.aspx

|||

Thank you.

Is there any limitation on number of databases. I mean, How many 4 GB databases can I have(Provided there is disk space).

I might be asking the basics, but need to finalize on a product architecture.

Thanks

|||

You can have as many 4 GB databases as your hardware allows. Needless to say, there is a practical limit to how many database can be managed and accessed given the other limitations of 1 CPU and 1 GB Ram.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.