sql nulls slow: application

Showing posts with label application. Show all posts

Sunday, March 25, 2012

ASP. NET vs. PL/SQL

Hey guys, I am trying to rewrite an application with an Active Server Pages front-end that calls Oracle WebDB v2.2 reports. The data is all stored in an Oracle 9i database. What are some of the benefits and drawbacks of redesigning the application using an ASP .NET or PL/SQL
implementation. The redesigned application should still retrieve the data from the Oracle 9i database. Any help with this is greatly appreciated.When it comes to database development PL/SQL has the advantage since it is very flexible with Oracle and it seperates the business logic from the interface. But it lacks presentation in a nice way.

ASP.NET on the other hand can be developed to display data in very nice way. Performance wise PL/SQL would be better since it directly runs in the database server.

If the presentation of the report is not an issue then go for PL/SQL, or else you can go for ASP.NET

But remember to eveluate your skill level also on both and decide on one.

asp, trusted authentication, sql server 2000

hi...

i'm trying to write a database application using asp (not .net) and need to have it connect to SQL server 2000 using windows integrated security and trusted authentication. i have the following asp code:

DIM dbConn
set dbConn = Server.CreateObject("ADODB.Connection")
DBconn.Open "Driver={SQL Server};Server=DBSERVER;Database=MyDatabase;Truste d_Connection=yes"

i can access the page from the local webserver (http://localhost/sample.asp) but cannot access it from another machine. when i access it via another machine, it prompts for a un/pw but still says:

"Login failed for user (null). Reason: Not associated with a trusted SQL server connection"

please help...thanksHowdy,

Try setting up an ODBC connection ( make it use NT authentication ) on the web box to the SQL box and see if the problem goes away.

That may help establish where the problem is. Also, do you have same version of MDAC on web server & SQL Server boxes?

Cheers,

SG.|||i do have an odbc connection to the sql database on the web box (which currently is an xp pro machine for testing purposes, once i finish it will be moved to a 2000 server box)...how do i get it to use that specific connection?|||Try the following connection string:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mydatabase;Data Source=dbserver

Also, your iis configuration may not be correct.|||After a good deal of troubleshooting, I have reason to believe that my IIS server is at fault. I do not know how to configure it correctly to use integrated security. I thought I had it correct - I do not allow Anonymous access, and under Authenticated access I have:

Digest authentication for Windows domain servers is checked (I've tried not using this, and recieved the same result)

Basic authentication (password is sent in clear text) is NOT checked

Integrated Windows authentication is checked.

When logging onto the ASP page, it does prompt for a user name and password. Upon entering my AD logon/password (which does have permission on the database), it errors out, reporting Login failed for user (null). This error is the same as it appears in SQL server's logs.

I can access it directly from the server without incident. Also, if i *do* allow anonymous access and use my credentials (as opposed to IUSER_blah), I can access it successfully. Otherwise, it reports the aforementioned error.

It might be mentioned that the machine I've been using to try to access the page is on XYZ domain, as is the IIS server - however, the SQL server is in the ABC domain. Does this matter?

Thursday, March 22, 2012

ASP- Db Connection problem

Hi there,
I have a web application developed in ASP and SQL-server. One of my clients wanted the SQL DB to reisde in a separate machine than the IIS box. Also he doesn't want the SQL - DB box to be protected from public access. So only the IIS box is assiged a public IP, and the SQL box is in the same network(LAN) as of the IIS box. Now using TCP/IP can I connect to the SQL server from my ASP? or should I have to use Named pipes?

Here is the Connection string that I used:
constr="Provider=SQLOLEDB;Network Library=DBMSSOCN;SERVER=xxx;Initial Catalog=mydb;User Id=user;Password=xxxxxx;"

When I use the above, I don't know what is happening.. but my IE animated picture at the top right corner is moving(i.e its talking to the server) without any response. Can anyone of you give me a clue why this is happening?
Also I am NOT receiving any error, like 'Connection timed out' , or Script Timed out. That makes me nervous.Try creating a dsn and test the connection and/or write a vb app that uses the ado data control and use the automated connection string creator and test the connection. If that works, copy the connection string from your ado data control to your asp page.|||I like to use TCP/IP to connect, even though you hear that Name Pipe is faster. The simplest thing to do to see if you can use TCP/IP from your IIS machine is to physically logon to your IIS machine and go to DOS and enter the PING command to your SQL Server machine. Make sure you PING by hostname and not by IP address. If you get a response, perfect use TCP/IP to connect. If you don't get a response and your SQL Server machine has a static IP address, then PING by IP Address. If that is fine then I would enter my SQL Server machine into an LMHost file.

ASP and SQL2005

Is this a good combination for application development?
Sure, if you're performing web development...do you mean ASP classic or ASP.NET? Either works great with SQL 2005...obviously, I can't say whether it's the best choice or not, cause I don't know your product specs, but it's definately a very popular, widely used combination.

Tuesday, March 20, 2012

ASP + SQL loads really slow over intranet

Hi guys,

I've created a web application using ASP together with SQL Server as
our db source, running through IIS 6 on a Winows Server 2003 platform.

This application retrieves a list of customer codes from our db, so
records returned could be as many as 2000+ for any single transaction.

The application runs fine for users from the same state. However, our
interstate colleagues have notice that it takes more than 3-4mins for
the page to load, while it only takes me < 2secs to load.

Our intranet server is located in the same state as I, so anyone from
within this state has no problems loading the page. All other states
are finding it unbearable.

I've done some debugging, and it appears to be a server factor.
I saved the page with the longest list to a local drive and opened it
locally in IE and it loads quickly.

Does anyone have any suggestions as to how to speed this application up
for our interstate users?
Any ideas would appreciated.

Thanks,
ShawnI would start by trying to identify the bottleneck - from what you've
said, it's not clear if this is an MSSQL issue. If I understand you,
both you and the other users are hitting the same IIS server, and
issuing the same queries, but they get the results slowly and you
don't. That could suggest that the delay is in serving the pages to the
remote users, not in retrieving the data from the database.

You can use the MSSQL Profiler to check the duration and execution plan
of your queries, to see if there's any difference depending on the user
requesting the data. If there is a difference, then hopefully the
execution plan will give you a clue as to where the problem is.

You might also want to post in an ASP/IIS group, if you haven't already
done so - many performance problems aren't due to only one issue, so
getting some ideas about how to investigate the other components of the
system would probably be useful.

Simon|||Hi Simon,

I did some debugging, and it took SQL Server the same about of time to
do complete the query for both local and interstate, and it's the
serving of the page that is definitely taking much longer on our
interstate servers.

Usually, most users would do a query that should return about 1000+
records, and than these records gets displayed into a table on the asp
page. The page (file size) itself is roughly around 110+kB, so it's not
exactly a BIG file to be tranferred. But then why the slow down?

Any advices/ideas on how to by-pass this problem?

Regards,
Shawn

Simon Hayes wrote:
> I would start by trying to identify the bottleneck - from what you've
> said, it's not clear if this is an MSSQL issue. If I understand you,
> both you and the other users are hitting the same IIS server, and
> issuing the same queries, but they get the results slowly and you
> don't. That could suggest that the delay is in serving the pages to
the
> remote users, not in retrieving the data from the database.
> You can use the MSSQL Profiler to check the duration and execution
plan
> of your queries, to see if there's any difference depending on the
user
> requesting the data. If there is a difference, then hopefully the
> execution plan will give you a clue as to where the problem is.
> You might also want to post in an ASP/IIS group, if you haven't
already
> done so - many performance problems aren't due to only one issue, so
> getting some ideas about how to investigate the other components of
the
> system would probably be useful.
> Simon|||"Shawn H" <ShawnStyler@.gmail.com> wrote in message
news:1112975388.441101.9010@.z14g2000cwz.googlegrou ps.com...
> Hi Simon,
> I did some debugging, and it took SQL Server the same about of time to
> do complete the query for both local and interstate, and it's the
> serving of the page that is definitely taking much longer on our
> interstate servers.
> Usually, most users would do a query that should return about 1000+
> records, and than these records gets displayed into a table on the asp
> page. The page (file size) itself is roughly around 110+kB, so it's not
> exactly a BIG file to be tranferred. But then why the slow down?
> Any advices/ideas on how to by-pass this problem?
> Regards,
> Shawn

<snip
No idea, unfortunately. Since the problem seems to be serving the pages,
you'll probably get better assistance in an IIS/ASP group.

Simon

ASP + SQL Loads Really Slow Over Intranet

Hi guys,
I've created a web application using ASP together with SQL Server as
our db source, running through IIS 6 on a Winows Server 2003 platform.
This application retrieves a list of customer codes from our db, so
records returned could be as many as 2000+ for any single transaction.
The application runs fine for users from the same state. However, our
interstate colleagues have notice that it takes more than 3-4mins for
the page to load, while it only takes me < 2secs to load.
Our intranet server is located in the same state as I, so anyone from
within this state has no problems loading the page. All other states
are finding it unbearable.
I've done some debugging, and it appears to be a server factor.
I saved the page with the longest list to a local drive and opened it
locally in IE and it loads quickly.
Does anyone have any suggestions as to how to speed this application up
for our interstate users?
Any ideas would appreciated.
Thanks,
ShawnHi
Returning 2000+ customer codes and putting it into a dropdown will make any
application unbearable. How do you find what you are looking for with a drop
down like that?
How are you retrieving the codes, a select * from ...or a select CustomerID
from ...?
Make sure you are not doing any data binding.
If the application is fast in the local state, it means that SQL Server does
all it's work in under 2 seconds, no matter what state, and then pushing the
resultant web page over the wire is your performance bottleneck. How big is
the resultant page? Anything more than 100Kb is generally considered bad.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Shawn H" <ShawnStyler@.gmail.com> wrote in message
news:1112934431.434915.93490@.o13g2000cwo.googlegroups.com...
> Hi guys,
>
> I've created a web application using ASP together with SQL Server as
> our db source, running through IIS 6 on a Winows Server 2003 platform.
>
> This application retrieves a list of customer codes from our db, so
> records returned could be as many as 2000+ for any single transaction.
>
> The application runs fine for users from the same state. However, our
> interstate colleagues have notice that it takes more than 3-4mins for
> the page to load, while it only takes me < 2secs to load.
>
> Our intranet server is located in the same state as I, so anyone from
> within this state has no problems loading the page. All other states
> are finding it unbearable.
>
> I've done some debugging, and it appears to be a server factor.
> I saved the page with the longest list to a local drive and opened it
> locally in IE and it loads quickly.
>
> Does anyone have any suggestions as to how to speed this application up
> for our interstate users?
> Any ideas would appreciated.
>
> Thanks,
> Shawn
>|||Hi Mike,
I'm not sure where you got the idea that I'm putting all the records
into a drop-down list, coz all I'm doing is just puting it into a
table.
I forgot to mention that to retrieve the records, I call a stored
procedure that does this:
"select fldCustCode, fldCustName (etc..)
from tbCust
where fldCustCode = @.custcode"
I think you might be right about the bottleneck being it has to send
the page back over the network.
I did a quick check, and the file size is just a little over 110+kB.
Is there anyway I can get around this?
Somehow I can reduce/compress the file size while it gets sent over the
network? (if thats even possible)
Regards,
Shawn
Mike Epprecht (SQL MVP) wrote:
> Hi
> Returning 2000+ customer codes and putting it into a dropdown will
make any
> application unbearable. How do you find what you are looking for with
a drop
> down like that?
> How are you retrieving the codes, a select * from ...or a select
CustomerID
> from ...?
> Make sure you are not doing any data binding.
> If the application is fast in the local state, it means that SQL
Server does
> all it's work in under 2 seconds, no matter what state, and then
pushing the
> resultant web page over the wire is your performance bottleneck. How
big is
> the resultant page? Anything more than 100Kb is generally considered
bad.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Shawn H" <ShawnStyler@.gmail.com> wrote in message
> news:1112934431.434915.93490@.o13g2000cwo.googlegroups.com...
as
platform.
transaction.
our
for
from
states
it
application up|||Shawn wrote on 7 Apr 2005 23:56:13 -0700:

> Hi Mike,
> I'm not sure where you got the idea that I'm putting all the records
> into a drop-down list, coz all I'm doing is just puting it into a
> table.
> I forgot to mention that to retrieve the records, I call a stored
> procedure that does this:
> "select fldCustCode, fldCustName (etc..)
> from tbCust
> where fldCustCode = @.custcode"
> I think you might be right about the bottleneck being it has to send
> the page back over the network.
> I did a quick check, and the file size is just a little over 110+kB.
Large tables are really noticeable when the bottleneck is the connection -
until the end table tag is received IE won't show anything. If you remove
the table tags for testing and just show plain text, you'll probably find
those users start seeing data almost immediately. Still, 110kB isn't much -
are you sure it's not 1.1MB? I've got a table displayed in my browser right
now with only a menu system, 6 columns (and mostly numbers in each of
those), and for around 100 rows that's 160kB. This page shows up on my home
PC in around 4-5 secs over a 512kbps ADSL line. I guess if your users have
dialup then it might take a few mins, but anything faster shouldn't.

> Is there anyway I can get around this?
> Somehow I can reduce/compress the file size while it gets sent over the
> network? (if thats even possible)
If you don't need all the data to be seen at once is to use paging - show
say 100 per page, with clickable page numbers to get other records.
If the data doesn't need to look nice you could look at pre tags and fixed
width spacing of lines.
If both ends support gzip compression then it's possible I guess.
Dan|||Hi Daniel,
The file is definitely 110+kB and not 1.1Mb, I wouldn't miss such a big
difference.
The asp page itself is pretty simple, it doesn't contain any menu(s) or
any images, it only contains two buttons and a table consisting of 8
columns and over 1000+ rows (each with some short text and small
numbers).
I still gotta find out how fast our interstate connections are, but
it's definitely not dial-up.
I'll keep trying ... but keep the suggestions coming!
Regards,
Shawn
Daniel Crichton wrote:
> Shawn wrote on 7 Apr 2005 23:56:13 -0700:
>
records
send
110+kB.
> Large tables are really noticeable when the bottleneck is the
connection -
> until the end table tag is received IE won't show anything. If you
remove
> the table tags for testing and just show plain text, you'll probably
find
> those users start seeing data almost immediately. Still, 110kB isn't
much -
> are you sure it's not 1.1MB? I've got a table displayed in my browser
right
> now with only a menu system, 6 columns (and mostly numbers in each of

> those), and for around 100 rows that's 160kB. This page shows up on
my home
> PC in around 4-5 secs over a 512kbps ADSL line. I guess if your users
have
> dialup then it might take a few mins, but anything faster shouldn't.
>
the
>
> If you don't need all the data to be seen at once is to use paging -
show
> say 100 per page, with clickable page numbers to get other records.
> If the data doesn't need to look nice you could look at pre tags and
fixed
> width spacing of lines.
> If both ends support gzip compression then it's possible I guess.
> Dan|||Shawn wrote on 8 Apr 2005 09:00:24 -0700:

> The file is definitely 110+kB and not 1.1Mb, I wouldn't miss such a big
> difference.
> The asp page itself is pretty simple, it doesn't contain any menu(s) or
> any images, it only contains two buttons and a table consisting of 8
> columns and over 1000+ rows (each with some short text and small
> numbers).
OK, I was just wondering as my HTML is quite large (I guess due to using
links and having a number of columns with around 50 characters each per
row). A basic table with 1000 rows, 8 columns, and 1 character per column is
around 90kB.

> I still gotta find out how fast our interstate connections are, but
> it's definitely not dial-up.
> I'll keep trying ... but keep the suggestions coming!
As your earlier posts state that the page comes up immediately for yourself
for the same data, then the bottleneck has to be something that's between
the other states and your server that your PC doesn't go through. Could be a
router or firewall somewhere in the path that's overloaded and is passing
data really slowly. Have you tried a traceroute from some of these locations
to your server to see what the response times are like from each router in
the path?
Dan|||some other things you might want to take a look at a well...
How often is the clients cache cleared?
Stylesheets...how big are the stylesheets being used?
Scripts .... are they set to "DEFER" correctly?
Message posted via http://www.webservertalk.com

ASP .NET Web Site Administration Tool Problem

Hi,

I have a problem when I use ASP .NET Web Application Administration tool. When I click the Security tab, I receive following message

"There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem:Unable to connect to SQL Server database."

In the same page, there is a button to go to Provider section. I clicked it, and I went to the Provider page. There, there were two links, which were: "Select a single provider for all site management data" and
"Select a different provider for each feature (advanced)". I clicked the earlier, and I was redirected to a page to select provider. There, there was a link, I clicked it and I receive following message:

Provider ManagementCould not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

After reading this message, I opened Command Prompt and went to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. There, I ran the aspnet_regsql command, and then ASP .NET SQL Server Setup Wizard was displayed. In the first page of this wizard I chose "Configure SQL Server for application services" and then I clicked "Next" button. In the second page I left all fields as they were and then I clicke"Next". In the third page I only clicked "Next" and then some process ran. Finally, in the last page I received following message:

Setup failed.

Exception:
Unable to connect to SQL Server database.

------------
Details of failure
------------

System.Web.HttpException: Unable to connect to SQL Server database. --> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
-- End of inner exception stack trace --
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install)
at System.Web.Management.SqlServices.Install(String database, SqlFeatures features, String connectionString)
at System.Web.Management.ConfirmPanel.Execute()
"

What should I do? I tried to use this administration tool on computer in my house and everything was just fine. Why do I have this problem in my office?

Hi,

It seems that you are not providing correct information about your SQL Server database on second page of that wizard. It should be properly pointing to the database which would be used by your application. When you have properly pointed it to that, it will then create some default tables and objects for you to use and enable this feature in your application.

|||

Yes, you are right! I made a mistake in step 2 of ASP .NET SQL Server Setup Wizard. Recently, I successfully went through all the steps. However, I still can't open the Security tab in ASP .NET Web Site Administration Tool. When I open the Provider section, I still receive the message below:

What should I do? I need to create a login functionality for my site in near future. Please help me...

|||

Hi haris101,

There, there were two links, which were: "Select a single provider for all site management data" and
"Select a different provider for each feature (advanced)". I clicked the earlier, and I was redirected to a page to select provider.

Open your machine.config and search for "localsqlserver" connection string. The default is configured to use sql server express database. So, if you are not using sql express, you need to modify this "localsqlserver" connection string or specify a new connection string for the default provider.

I suggestion you read this walk-through first:http://msdn2.microsoft.com/en-us/library/879kf95c(VS.80).aspx

Hope my suggestion helps

|||

hey haris

im having the same problem -
i can get the web site to run queries
but i cant get the website administration to connect
did you ever find out the solution??
would love to know your experience

thanks
toy

|||

I'm also having a similar problem, I tried to connect the aspnet_regsql to my sql 2005 server on the network but it keeps telling me it can't connect. and i'm not sure why that is. I already have information for my web app stored in this server so i know that it is running just fine. if anyone can help that would be great, im running the vs 2005 pro with the 2.0 framework.

Thank,

Shane S.
FDOT

|||

Bo Chen – MSFT:
Hi haris101,
There, there were two links, which were: "Select a single provider for all site management data" and
"Select a different provider for each feature (advanced)". I clicked the earlier, and I was redirected to a page to select provider.
Open your machine.config and search for "localsqlserver" connection string. The default is configured to use sql server express database. So, if you are not using sql express, you need to modify this "localsqlserver" connection string or specify a new connection string for the default provider.
I suggestion you read this walk-through first:http://msdn2.microsoft.com/en-us/library/879kf95c(VS.80).aspx
Hope my suggestion helps

Thanks Bo! You are right! Yes

For others who experience the same problem like mine, all you have to do is modify machine.config file in ...\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG (this is the default location). Scroll down until you find

<connectionStrings> <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /></connectionStrings>

What I did is changing the connection string to "Data Source=IT-HARIS\SQLEXPRESS;Integrated Security=True" since "IT-HARIS\SQLEXPRESS" is my SQL Server name and I use windows authentication to log on to this server. I hope this works for you too.

Best regards,

Haris

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

1 BACKGROUND sa . . NULL LAZY WRITER 21688 0 06/14 17:57:59 1
2 sleeping sa . . NULL LOG WRITER 200766 0 06/14 17:57:59 2
3 BACKGROUND sa . . NULL LOCK MONITOR 1203 0 06/14 17:57:59 3
4 BACKGROUND sa . . master SIGNAL HANDLER 47 0 06/14 17:57:59 4
5 BACKGROUND sa . . master TASK MANAGER 0 849 06/14 17:57:59 5
6 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 6
7 sleeping sa . . NULL CHECKPOINT SLEEP 252156 17092 06/14 17:57:59 7
8 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 8
9 BACKGROUND sa . . master TASK MANAGER 0 1595 06/14 17:57:59 9
10 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 10
11 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 11
12 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 12
13 BACKGROUND sa . . master TASK MANAGER 0 388 06/14 17:57:59 13
14 BACKGROUND sa . . master TASK MANAGER 0 2129 06/14 17:57:59 14
15 BACKGROUND sa . . master TASK MANAGER 0 2176 06/14 17:57:59 15
17 BACKGROUND sa . . master TASK MANAGER 0 517 06/14 17:57:59 17
51 sleeping NT AUTHORITY\SYSTEM W2K92 . msdb AWAITING COMMAND 62 37 06/14 17:58:37 SQLAgent - Generic Refresher 51
52 sleeping NT AUTHORITY\SYSTEM W2K92 . msdb AWAITING COMMAND 1192429 29 09/13 12:57:53 SQLAgent - Alert Engine 52
53 sleeping WCAS PCOFFNONGGK8881 . WCASR2 AWAITING COMMAND 61 16 09/13 11:53:43 TESS 53
54 sleeping SA PCOFFNCHANWCS81 . TEMISCS AWAITING COMMAND 578 8 09/13 12:48:39 Temis-CS 54
55 sleeping SA PCOFFNHONGHSC81 . TEMISCS AWAITING COMMAND 218 3 09/13 12:07:14 Temis-CS 55
56 sleeping SA PCOFFNCHANWCS81 . TEMISCS AWAITING COMMAND 2080 228 09/13 12:49:45 Temis-CS 56
57 sleeping TESS W2K92 . TESS AWAITING COMMAND 29175 3 09/13 12:55:11 .Net SqlClient Data Provider 57
58 sleeping W2K92\w2k92_remote W2K92 . TESS AWAITING COMMAND 63 5 09/13 12:49:03 MS SQLEM 58
59 RUNNABLE W2K92\w2k92_remote W2K92 . TESS SELECT INTO 156 9 09/13 12:58:00 SQL Query Analyzer 59
60 sleeping TESS W2K92 . TESS AWAITING COMMAND 5549 0 09/13 12:58:02 .Net SqlClient Data Provider 60
61 sleeping sa W2K92 . WCASR2 AWAITING COMMAND 9422 408 09/13 12:57:57 Microsoft(R) Windows (R) 2000 Operating System 61
62 sleeping sa W2K92 . WCASR2 AWAITING COMMAND 1781 113 09/13 12:57:57 Microsoft(R) Windows (R) 2000 Operating System 62

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

sql

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

ASP .Net 2.0 SQLServer MembershipProvider

Hi,

Currently I am developing a web application which takes a SQL Database file mdf as backend. I use SQL Server express edition as backend. I use SQL Membership provider as well as Role provider. All the data related to membership & the application is combined in a common database and exists as a single mdf file which I have attached through SQL Server Express Edtion.

My problem is I am able to access the application Tables & Stored procedures etc but not getting connected with the Membership methods.

Can any one help me in this aspect. The connection string what I have done for the attaching the database file is as follows

<addname="connectionstring"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirectory|JsskDb.mdf";Trusted_Connection=Yes"providerName="System.Data.SqlClient"/>

<addname="MyProviderConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|JsskDb.mdf;Integrated Security=SSPI;"providerName="System.Data.SqlClient"/>

The first one works while the later doesn't work.

Help please,

Thanks,

Uma Ramiya

Hi Ramiya,

According to the code you posted, I think the way you create your connection string is right, It can be declared in the connectionString node but not for Membership settings, it should be declared in the membership node. Here's the correct way.

Hope this helps. Thanks.

ASP - SQL server connection Issue

Hello All,

I am building an application using SQL server 2000. When I was using SQL database on my local machine it works great but now I moved the database on the server and as soon as I try to connect it gives me this error message.

What I am doing wrong?

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)**

Thanks

Check web.config if it is pointing to the right server.

bullpit

|||

Bingo...

Thanks

Asking the user a question

Hello all,

I have a client with an application that they have purchased, it
stores its data on SQL server 2000 and so I am able to do reports and
some small VB apps that use the data. I can't however make any
significant changes to the app itself, just what it allows you to
configure.

The system records jobs, when a job is closed the client want a prompt
to pop-up asking if they want to record that the job is closed in a
special log file. I suggested that we use a checkbox (We can add
custom fields to the app) to record this, then I could write a trigger
that checks that field and adds it to the log file if it's 1, but no
they want a yes/no message box type thing popping up.

Is it possible to display (on the users screen) and get input back
from a yes/no message box in a SQL Server trigger? As far as I can
tell it isn't but it would be great if I were wrong.

Any help/suggestions greatly appreciated!!!

Thanks,

Bob.SQL Server can't invoke anything in your UI. That's something you have to do
in client-side code.

--
David Portas
SQL Server MVP
--|||Robert Hogan (busrhogan@.hotmail.com) writes:
> Is it possible to display (on the users screen) and get input back
> from a yes/no message box in a SQL Server trigger? As far as I can
> tell it isn't but it would be great if I were wrong.

In theory, yes. But for crying out loud, don't do it!

You could use sp_OAmethod and friends to run a remote OLE object
that would wait for the reponse.

But while waiting for user input - which could take several hours or
days - you are in the middle of a transaction, data will be uncommitted
and locks will be held. Could have sever impact on the situation.

A variant would be to have the OLE thing asynchronous, so that the
trigger does not wait for input, and the OLE thing would then update
the database itself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
"Robert Hogan" <busrhogan@.hotmail.com> wrote in message
news:eb62f112.0501171614.cb68c36@.posting.google.co m...
> Hello all,
> I have a client with an application that they have purchased, it
> stores its data on SQL server 2000 and so I am able to do reports and
> some small VB apps that use the data. I can't however make any
> significant changes to the app itself, just what it allows you to
> configure.
> The system records jobs, when a job is closed the client want a prompt
> to pop-up asking if they want to record that the job is closed in a
> special log file.

Well...
This is the problem with buying a package.
They do what the people wrote them guessed you wanted.
How close that is to what you do depends on how good he guessed.

> I suggested that we use a checkbox (We can add
> custom fields to the app) to record this, then I could write a trigger
> that checks that field and adds it to the log file if it's 1, but no
> they want a yes/no message box type thing popping up.

No chance.
You/They have 4 practical options:

1) Bung the field on and use a trigger to write the stuff as you suggest.

2) Contact the people wrote the package and pay them to make a custom
change.
If they're happy to do this then remember upgrades and expect to pay
through the nose for that confirmation box.

3) Re-write the package from scratch.
Probably financially unattractive unless they have a lot of reports and
customisation in mind.

4) Work out what that screen does and write one yourself that replaces it.
Some packages allow you to have custom screens, I'd guess maybe this
isn't one so maybe not so practical.
If that one screen and your other additions is all whoever closes jobs
uses then maybe it is a go-er.
> Is it possible to display (on the users screen) and get input back
> from a yes/no message box in a SQL Server trigger? As far as I can
> tell it isn't but it would be great if I were wrong.
> Any help/suggestions greatly appreciated!!!

Next time they buy a package..
Remember the hidden costs.
It's can be far cheaper to go for a bespoke app in the long run.

> Thanks,
> Bob.

--
Regards,
Andy O'Neillsql

ASKING FOR JDBC AND SQL92 COMPATIBILITY

We try to compare Postegesql/Oracle/Sql Server/Ingres
for a local application developped in Java.
Could you confirm by mail that oracle database server is SQL92 (Level entry)
compatible, and support JDBC1/JDBC2 and JDBC3.
Thanks for your respons.
Regards,
Lionel MICHAUT
Areva T&D
"villeurbanne" <villeurbanne@.discussions.microsoft.com> wrote in message
news:8559FF49-4432-49FB-A9DD-2A902C1F9B2E@.microsoft.com...
> We try to compare Postegesql/Oracle/Sql Server/Ingres
> for a local application developped in Java.
> Could you confirm by mail that oracle database server is SQL92 (Level
> entry)
> compatible, and support JDBC1/JDBC2 and JDBC3.
Looks like you bought a ticket to the wrong show.
|||Hi
Yes, 10g is....got a lot of money?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uXHU7PJMFHA.1144@.TK2MSFTNGP09.phx.gbl...
> "villeurbanne" <villeurbanne@.discussions.microsoft.com> wrote in message
> news:8559FF49-4432-49FB-A9DD-2A902C1F9B2E@.microsoft.com...
> Looks like you bought a ticket to the wrong show.
>
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ep%23$lEMMFHA.3760@.TK2MSFTNGP12.phx.gbl...
> Hi
> Yes, 10g is....got a lot of money?
One needle deserves another:
Apparently alot of companies do

|||Sorry, I did copy my question from my oracle support web support a little to
quickly.
My real question was :
Could you confirm that SQL Serveur (last release) is SQL92 (Level entry)
compatible, and support JDBC1/JDBC2 and JDBC3.
Thanks for your respons.
Regards,
Lionel MICHAUT
Areva T&D

ASKING FOR JDBC AND SQL92 COMPATIBILITY

We try to compare Postegesql/Oracle/Sql Server/Ingres
for a local application developped in Java.
Could you confirm by mail that oracle database server is SQL92 (Level entry)
compatible, and support JDBC1/JDBC2 and JDBC3.
Thanks for your respons.
Regards,
Lionel MICHAUT
Areva T&D"villeurbanne" <villeurbanne@.discussions.microsoft.com> wrote in message
news:8559FF49-4432-49FB-A9DD-2A902C1F9B2E@.microsoft.com...
> We try to compare Postegesql/Oracle/Sql Server/Ingres
> for a local application developped in Java.
> Could you confirm by mail that oracle database server is SQL92 (Level
> entry)
> compatible, and support JDBC1/JDBC2 and JDBC3.
Looks like you bought a ticket to the wrong show.|||Hi
Yes, 10g is....got a lot of money?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uXHU7PJMFHA.1144@.TK2MSFTNGP09.phx.gbl...
> "villeurbanne" <villeurbanne@.discussions.microsoft.com> wrote in message
> news:8559FF49-4432-49FB-A9DD-2A902C1F9B2E@.microsoft.com...
> Looks like you bought a ticket to the wrong show.
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ep%23$lEMMFHA.3760@.TK2MSFTNGP12.phx.gbl...
> Hi
> Yes, 10g is....got a lot of money?
One needle deserves another:
Apparently alot of companies do

ASKING FOR JDBC AND SQL92 COMPATIBILITY

We try to compare Postegesql/Oracle/Sql Server/Ingres
for a local application developped in Java.
Could you confirm by mail that oracle database server is SQL92 (Level entry)
compatible, and support JDBC1/JDBC2 and JDBC3.
Thanks for your respons.
Regards,
Lionel MICHAUT
Areva T&D"villeurbanne" <villeurbanne@.discussions.microsoft.com> wrote in message
news:8559FF49-4432-49FB-A9DD-2A902C1F9B2E@.microsoft.com...
> We try to compare Postegesql/Oracle/Sql Server/Ingres
> for a local application developped in Java.
> Could you confirm by mail that oracle database server is SQL92 (Level
> entry)
> compatible, and support JDBC1/JDBC2 and JDBC3.
Looks like you bought a ticket to the wrong show.|||Hi
Yes, 10g is....got a lot of money?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uXHU7PJMFHA.1144@.TK2MSFTNGP09.phx.gbl...
> "villeurbanne" <villeurbanne@.discussions.microsoft.com> wrote in message
> news:8559FF49-4432-49FB-A9DD-2A902C1F9B2E@.microsoft.com...
> > We try to compare Postegesql/Oracle/Sql Server/Ingres
> > for a local application developped in Java.
> > Could you confirm by mail that oracle database server is SQL92 (Level
> > entry)
> > compatible, and support JDBC1/JDBC2 and JDBC3.
> Looks like you bought a ticket to the wrong show.
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ep%23$lEMMFHA.3760@.TK2MSFTNGP12.phx.gbl...
> Hi
> Yes, 10g is....got a lot of money?
One needle deserves another:
Apparently alot of companies do:)|||Sorry, I did copy my question from my oracle support web support a little to
quickly.
My real question was :
Could you confirm that SQL Serveur (last release) is SQL92 (Level entry)
compatible, and support JDBC1/JDBC2 and JDBC3.
Thanks for your respons.
Regards,
Lionel MICHAUT
Areva T&D

Monday, March 19, 2012

Ascending Date Column Statistics "dbcc traceon( 2389 )"

I am having trouble with query performance on SQL Server 2005. The database
is a basic book ordering application. A simple one to many Orders to Order
Items. The orders table contains 80,000 rows and the Order Items table
contains 280,000 rows. The problem query contains an inner join with Orders
and Order Items with where clause on Order Date. The problem is when you
execute the query on old data the inner join is based on a hash (1 second
execution). When you execute the query on new data the inner join is based
on a nested loop (2 minute execution). The reason for this is the stats.
The stats for the order date column do not contain the new data so the
estimated rows for the given date range is 1 (when the query actually returns
1320 rows). If I update the stats for order date the query will perform as
expected (hash join). The following blog explains the issue.
http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
In order to fix this issue it appears I have to schedule a manual update of
the stats or use dbcc traceon( 2389 ). This application ran on SQL server
2000 for 4 years with no problems. Why did I not see an issue with SQL
server 2000? Does anyone have any better ideas except manually updating the
stats?First of all, have you tried a HASH JOIN hint? If the hash is working well,
force it.
I have also had a lot of statistics problems in some of the applications
I've been working on, and although I haven't had a chance to try these trace
flags yet, I have found workarounds to every issue (generally, involving
query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
not doing as well as SQL Server 2000 did, but overall I think performance is
improved in most scenarios. Hopefully as MS continues to evaluate use cases
these issues will go away and we can go back to thinking about other things!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>I am having trouble with query performance on SQL Server 2005. The
>database
> is a basic book ordering application. A simple one to many Orders to
> Order
> Items. The orders table contains 80,000 rows and the Order Items table
> contains 280,000 rows. The problem query contains an inner join with
> Orders
> and Order Items with where clause on Order Date. The problem is when you
> execute the query on old data the inner join is based on a hash (1 second
> execution). When you execute the query on new data the inner join is
> based
> on a nested loop (2 minute execution). The reason for this is the stats.
> The stats for the order date column do not contain the new data so the
> estimated rows for the given date range is 1 (when the query actually
> returns
> 1320 rows). If I update the stats for order date the query will perform
> as
> expected (hash join). The following blog explains the issue.
> http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> In order to fix this issue it appears I have to schedule a manual update
> of
> the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> 2000 for 4 years with no problems. Why did I not see an issue with SQL
> server 2000? Does anyone have any better ideas except manually updating
> the
> stats?
>|||Thanks for the help. The HASH Join hint does work.
"Adam Machanic" wrote:
> First of all, have you tried a HASH JOIN hint? If the hash is working well,
> force it.
> I have also had a lot of statistics problems in some of the applications
> I've been working on, and although I haven't had a chance to try these trace
> flags yet, I have found workarounds to every issue (generally, involving
> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
> not doing as well as SQL Server 2000 did, but overall I think performance is
> improved in most scenarios. Hopefully as MS continues to evaluate use cases
> these issues will go away and we can go back to thinking about other things!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "achopp" <achopp@.discussions.microsoft.com> wrote in message
> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
> >I am having trouble with query performance on SQL Server 2005. The
> >database
> > is a basic book ordering application. A simple one to many Orders to
> > Order
> > Items. The orders table contains 80,000 rows and the Order Items table
> > contains 280,000 rows. The problem query contains an inner join with
> > Orders
> > and Order Items with where clause on Order Date. The problem is when you
> > execute the query on old data the inner join is based on a hash (1 second
> > execution). When you execute the query on new data the inner join is
> > based
> > on a nested loop (2 minute execution). The reason for this is the stats.
> > The stats for the order date column do not contain the new data so the
> > estimated rows for the given date range is 1 (when the query actually
> > returns
> > 1320 rows). If I update the stats for order date the query will perform
> > as
> > expected (hash join). The following blog explains the issue.
> >
> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> >
> > In order to fix this issue it appears I have to schedule a manual update
> > of
> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
> > server 2000? Does anyone have any better ideas except manually updating
> > the
> > stats?
> >
>
>|||Or possibly the date_correlation_optimization_option database option might help. See also
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/275fb44b-417a-426a-bf7c-f23308ed72c0.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:609F8143-0EB4-4400-B84A-F3D75FBDDE95@.microsoft.com...
> Thanks for the help. The HASH Join hint does work.
> "Adam Machanic" wrote:
>> First of all, have you tried a HASH JOIN hint? If the hash is working well,
>> force it.
>> I have also had a lot of statistics problems in some of the applications
>> I've been working on, and although I haven't had a chance to try these trace
>> flags yet, I have found workarounds to every issue (generally, involving
>> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
>> not doing as well as SQL Server 2000 did, but overall I think performance is
>> improved in most scenarios. Hopefully as MS continues to evaluate use cases
>> these issues will go away and we can go back to thinking about other things!
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "achopp" <achopp@.discussions.microsoft.com> wrote in message
>> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>> >I am having trouble with query performance on SQL Server 2005. The
>> >database
>> > is a basic book ordering application. A simple one to many Orders to
>> > Order
>> > Items. The orders table contains 80,000 rows and the Order Items table
>> > contains 280,000 rows. The problem query contains an inner join with
>> > Orders
>> > and Order Items with where clause on Order Date. The problem is when you
>> > execute the query on old data the inner join is based on a hash (1 second
>> > execution). When you execute the query on new data the inner join is
>> > based
>> > on a nested loop (2 minute execution). The reason for this is the stats.
>> > The stats for the order date column do not contain the new data so the
>> > estimated rows for the given date range is 1 (when the query actually
>> > returns
>> > 1320 rows). If I update the stats for order date the query will perform
>> > as
>> > expected (hash join). The following blog explains the issue.
>> >
>> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
>> >
>> > In order to fix this issue it appears I have to schedule a manual update
>> > of
>> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
>> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
>> > server 2000? Does anyone have any better ideas except manually updating
>> > the
>> > stats?
>> >
>>

Wednesday, March 7, 2012

AS hangs when running a complex mdx query

Hi all,

I am having a problem with Analysis Services. Currently we are developing an analysis application that enables a user to view up to 5 year survival for patients.

I have written the following MDX to perform the calculated measure:

Code Snippet

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL

// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)

CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case

At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):

Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).

NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.

The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.

When IsEmpty( [Survival Time].CurrentMember.PrevMember )

Then ( Root( [Survival Time] ), [N Total Deaths] )

/*
This is the survival censor count at the top ("root") level of survival time,
*/

Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:

(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month

( [Survival Time].CurrentMember.PrevMember, [Number at risk] )
- [N Total Deaths]

)

End
);
END SCOPE;

//--

CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case

// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty( [Survival Time].CurrentMember.PrevMember )

Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)

Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case

// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0

// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )

Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].CurrentMember.PrevMember, [Number at risk] ) - ( [Survival Time].CurrentMember,[N Noncensored Deaths]) ) /
([Survival Time].CurrentMember.PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].CurrentMember.PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;

When I put the Calculated Measure then the survival time dimension in the x axis in the pivot table, it works fine. But when I put a dimension in the filter and choose multiple items (not all and not only one) it tries to build and run the query but seems to hang.

Has anyone else experienced this sort of thing before?

Would it be something to do with the MDX?

I have been struggling with this for a while and not been able to work it out. ANY suggestions would be fantastic.

If you need further information, let me know.

Steve

I have not gone through your code in too much detail, but you main issue is going to be the .CurrentMember function.

When you have mulitple members on the WHERE clause (which is what happens when you filter by more than one member), you do not have a single current member, you actually have a set of current members. SSAS 2005 just does not handle this well. See this blog post for more details: http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx it might give you some hints on how to alter your code.

|||

Darren,

Thanks for your reply. I tried going to that blog post but couldn't open the page. Are you able to provide another location for the blog?

Cheers,

Stephen

|||

Yeah, I found out that the whole of sqljunkies.com appears to have gone off the air, no word on when or if it will return.

You can find an archived copy of this post in the web archive at http://web.archive.org/web/20070515145719/http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

|||

Thanks Darren. I found the blog but am not sure how to fix my code.

I actually tried a couple of things with no success. I removed the .CurrentMember from my code (see code snippet) and it worked for single select queries but it still hangs for multiselect.

Code Snippet

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

CALCULATE;

//**************************************************************************************

// SURVIVAL

// Censor flag = 1 if censored, 0 if noncensored (or true failure),

// NULL if there is data error (death date <= diagnosis date)

CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]

AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],

FORMAT_STRING = "#",

VISIBLE = 0 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]

AS 0,

VISIBLE = 1;

SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =

(

Case

At the first survival time (defined as Null, i.e. 1st row with "Blank" time,

when there is no previous month; PrevMember is empty):

Number at risk is simply total number of valid death dates (both censored and uncensored)

in the dataset, which is the total number of patients with censor flag values of

either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).

NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died

by this date are censored and assigned a death date of June 1, 2006.

The censor flag is set to null only when death date is same as or EARLIER than

date of diagnosis, i.e. excludes patients diagnosed during autopsy.

When IsEmpty([Survival Time].PrevMember )

Then ( Root( [Survival Time] ), [N Total Deaths] )

This is the survival censor count at the top ("root") level of survival time,

Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:

(

// Number at risk from previous month,

// minus total number of deaths (both censored and uncensored)

// during current month

([Survival Time].PrevMember, [Number at risk] )

- [N Total Deaths]

)

End

);

END SCOPE;

//--

CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]

AS 100,

FORMAT_STRING = '0.0',

VISIBLE = 1;

SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =

(

Case

// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):

When IsEmpty([Survival Time].PrevMember )

Then (

(

( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])

)

)*100

)

Else // At subsequent survival times ( t > 0, censor <> null ):

(

Case

// If everyone's dead (at right end of time(x) axis)

When [Number at risk] = 0

// Then use survival from previous month (PrevMember)

Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )

Else (

(

// Number at risk from previous month, minus true failures (noncensored deaths),

// divided by Number at risk from previous month ...

(

( ([Survival Time].PrevMember, [Number at risk] ) - ([Survival Time],[N Noncensored Deaths]) ) /

([Survival Time].PrevMember, [Number at risk]

)

// then multiplied by survival from previous month

* ([Survival Time].PrevMember, [Survival])

)

End

)

End

); END SCOPE;

I appreciate you helping me on this.

Cheers,

Steve

|||

Sorry, I was not entirely clear. While removing the explicit CurrentMember calls is good, there is still an implied one for the .PrevMember calls. The query engine still needs a single member context to calculate a PrevMember. There is a way to do this, but it has the unforutnate side effect of slowing down your calc for single member select. What you would need to do is to wrap your case statement (the bit after the equals inside your scope statement) as follows:

GENERATE( EXISTING [Survival Time].Members, <case statement> )

The Existing operator returns the set of one or more survival time members that are in context for the current query and the generate function essentially loops over the set and evaluates the case statement for each one. The results are then aggregated together and returned.

|||

Hi Darren,

My guess is that multi-select is not occurring on the [Survival Time] dimension, otherwise there should have been an error like: "The MDX function CURRENTMEMBER failed because the coordinate for .. attribute contains a set", as mentioned in Mosha's blog. The scenario described by Steve suggests that [Survival Time] is on columns, and some other dimension(s) (which aren't mentioned in the script) are being multi-selected in the filter field - but Steve could confirm this? It might be useful to know the client tool (presumably some flavor of Excel).

|||

Hi Deepak,

You are right. We are selecting dimensions such as age, location etc.

We are using Dundas for our client tool.

Cheers,

Steve

|||

Steve,

Thanks for the clarification - to confirm the exact Dundas product, is it Dundas Chart for .NET - OLAP Services?

|||

Deepak,

It is Dundas Chart for .NET - OLAP Service (5.5).

Thanks,

Steve

|||

Here are some initial ideas for improving script performance - with more information, these could be elaborated:

It might be possible to handle the first [Survival Time] member using static scoping, rather than a run-time case statement, as discussed in this article. What is the structure of [Survival Time] - how many levels does it have?

Not sure which measures are cube vs. calculated, but if [Measures].[N Total Deaths] and [Measures].[N Censored Deaths] are cube measures, [MEASURES].[N Noncensored Deaths] could be implemented as a cube measure as well. Could you describe the fact table/measure group and associated measures?

Using running sum calculations which leverage block computation - it looks like [MEASURES].[Number at risk] could be implemented using a running sum of [N Total Deaths]?

|||

Hi Deepak,

I have run the query when it works and when it hangs. And the following MDX queries are what resulted:

Working:

SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( ( [Year of diagnosis].[Year of diagnosis].[All] ), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )

Not Working (hangs/uses up all resources):

SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( VISUALTOTALS({ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )

Thanks for your help so fay.

Steve

|||

Hi Steve,

Just wanted to clarify a couple of things:

- Is [Survival Time] just a single level (month) dimension - and approx. how many members?

- The VisualTotals() seem to be superfluous, so could you check whether the 2nd query still hangs without VisualTotals(), like:

SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, { [Measures].[Survival] } ) } ON ROWS FROM [Survival] WHERE ( { [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }, ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )

|||

Hi Deepak,

The [Survival Time] dimension is a single level dimension. It is a count of months from a starting date to a death date.

I also ran the query from the browser in SQL Server Management Studio which dropped Visual Totals and it still hung.

Thanks again for your help.

Cheers,

Steve

|||

I'm not sure you need any of the VisualTotals functions. And unless you have overriden the default member settings on some of your dimensions, explicit referencing the default members would be redundant. The following two queries should be equivalent to the last 2 that you posted.

SELECT

NON EMPTY

{

{[Survival Time].[Survival Time].[All]}

, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS

, NON EMPTY

{[Cancer].[Cancers].[All]

, [Cancer].[Cancers].[Stream].members}

ON ROWS

FROM [Survival]

WHERE

( [Measures].[Survival],[Year of diagnosis].[Year of diagnosis].[All] );

--Was not Working

SELECT

NON EMPTY

{

{[Survival Time].[Survival Time].[All]}

, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS

, NON EMPTY

{[Cancer].[Cancers].[All]

, [Cancer].[Cancers].[Stream].members}

ON ROWS

FROM [Survival]

WHERE ( [Measures].[Survival],

{ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001]

, [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] })

AS hangs when running a complex mdx querie

Hi all,

I am having a problem with Analysis Services. Currently we are developing an analysis application that enables a user to view up to 5 year survival for patients.

I have written the following MDX to perform the calculated measure:

Code Snippet

// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)

CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case

At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):

NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.

The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.

When IsEmpty( [Survival Time].CurrentMember.PrevMember )

Then ( Root( [Survival Time] ), [N Total Deaths] )

/*
This is the survival censor count at the top ("root") level of survival time,
*/

Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:

(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month

( [Survival Time].CurrentMember.PrevMember, [Number at risk] )
- [N Total Deaths]

)

End
);
END SCOPE;

//--

CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case

// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty( [Survival Time].CurrentMember.PrevMember )

Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)

Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case

// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0

// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )

Has anyone else experienced this sort of thing before?

Would it be something to do with the MDX?

I have been struggling with this for a while and not been able to work it out. ANY suggestions would be fantastic.

If you need further information, let me know.

Steve

I have not gone through your code in too much detail, but you main issue is going to be the .CurrentMember function.

|||

Darren,

Thanks for your reply. I tried going to that blog post but couldn't open the page. Are you able to provide another location for the blog?

Cheers,

Stephen

|||

Yeah, I found out that the whole of sqljunkies.com appears to have gone off the air, no word on when or if it will return.

You can find an archived copy of this post in the web archive at http://web.archive.org/web/20070515145719/http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

|||

Thanks Darren. I found the blog but am not sure how to fix my code.

I actually tried a couple of things with no success. I removed the .CurrentMember from my code (see code snippet) and it worked for single select queries but it still hangs for multiselect.

Code Snippet

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

CALCULATE;

//**************************************************************************************

// SURVIVAL

// Censor flag = 1 if censored, 0 if noncensored (or true failure),

// NULL if there is data error (death date <= diagnosis date)

CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]

AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],

FORMAT_STRING = "#",

VISIBLE = 0 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]

AS 0,

VISIBLE = 1;

SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =

(

Case

At the first survival time (defined as Null, i.e. 1st row with "Blank" time,

when there is no previous month; PrevMember is empty):

Number at risk is simply total number of valid death dates (both censored and uncensored)

in the dataset, which is the total number of patients with censor flag values of

either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).

NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died

by this date are censored and assigned a death date of June 1, 2006.

The censor flag is set to null only when death date is same as or EARLIER than

date of diagnosis, i.e. excludes patients diagnosed during autopsy.

When IsEmpty([Survival Time].PrevMember )

Then ( Root( [Survival Time] ), [N Total Deaths] )

This is the survival censor count at the top ("root") level of survival time,

Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:

(

// Number at risk from previous month,

// minus total number of deaths (both censored and uncensored)

// during current month

([Survival Time].PrevMember, [Number at risk] )

- [N Total Deaths]

)

End

);

END SCOPE;

//--

CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]

AS 100,

FORMAT_STRING = '0.0',

VISIBLE = 1;

SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =

(

Case

// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):

When IsEmpty([Survival Time].PrevMember )

Then (

(

( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])

)

)*100

)

Else // At subsequent survival times ( t > 0, censor <> null ):

(

Case

// If everyone's dead (at right end of time(x) axis)

When [Number at risk] = 0

// Then use survival from previous month (PrevMember)

Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )

Else (

(

// Number at risk from previous month, minus true failures (noncensored deaths),

// divided by Number at risk from previous month ...

(

( ([Survival Time].PrevMember, [Number at risk] ) - ([Survival Time],[N Noncensored Deaths]) ) /

([Survival Time].PrevMember, [Number at risk]

)

// then multiplied by survival from previous month

* ([Survival Time].PrevMember, [Survival])

)

End

)

End

); END SCOPE;

I appreciate you helping me on this.

Cheers,

Steve

|||

GENERATE( EXISTING [Survival Time].Members, <case statement> )

|||

Hi Darren,

|||

Hi Deepak,

You are right. We are selecting dimensions such as age, location etc.

We are using Dundas for our client tool.

Cheers,

Steve

|||

Steve,

Thanks for the clarification - to confirm the exact Dundas product, is it Dundas Chart for .NET - OLAP Services?

|||

Deepak,

It is Dundas Chart for .NET - OLAP Service (5.5).

Thanks,

Steve

|||

Here are some initial ideas for improving script performance - with more information, these could be elaborated:

Using running sum calculations which leverage block computation - it looks like [MEASURES].[Number at risk] could be implemented using a running sum of [N Total Deaths]?

|||

Hi Deepak,

I have run the query when it works and when it hangs. And the following MDX queries are what resulted:

Working:

Not Working (hangs/uses up all resources):

SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( VISUALTOTALS({ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )

Thanks for your help so fay.

Steve

|||

Hi Steve,

Just wanted to clarify a couple of things:

- Is [Survival Time] just a single level (month) dimension - and approx. how many members?

- The VisualTotals() seem to be superfluous, so could you check whether the 2nd query still hangs without VisualTotals(), like:

SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, { [Measures].[Survival] } ) } ON ROWS FROM [Survival] WHERE ( { [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }, ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )

|||

Hi Deepak,

The [Survival Time] dimension is a single level dimension. It is a count of months from a starting date to a death date.

I also ran the query from the browser in SQL Server Management Studio which dropped Visual Totals and it still hung.

Thanks again for your help.

Cheers,

Steve

|||

SELECT

NON EMPTY

{

{[Survival Time].[Survival Time].[All]}

, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS

, NON EMPTY

{[Cancer].[Cancers].[All]

, [Cancer].[Cancers].[Stream].members}

ON ROWS

FROM [Survival]

WHERE

( [Measures].[Survival],[Year of diagnosis].[Year of diagnosis].[All] );

--Was not Working

SELECT

NON EMPTY

{

{[Survival Time].[Survival Time].[All]}

, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS

, NON EMPTY

{[Cancer].[Cancers].[All]

, [Cancer].[Cancers].[Stream].members}

ON ROWS

FROM [Survival]

WHERE ( [Measures].[Survival],

{ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001]

, [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] })

Sunday, March 25, 2012

ASP. NET vs. PL/SQL

asp, trusted authentication, sql server 2000

Thursday, March 22, 2012

ASP- Db Connection problem

ASP and SQL2005

Tuesday, March 20, 2012

ASP + SQL loads really slow over intranet

ASP + SQL Loads Really Slow Over Intranet

ASP .NET Web Site Administration Tool Problem

ASP .NET Application is Hanging when number of user grows

ASP .NET Application is Hanging when number of user grows

ASP .NET Application is Hanging when number of user grows

ASP .NET Application is Hanging when number of user grows

ASP .Net 2.0 SQLServer MembershipProvider

ASP - SQL server connection Issue

Asking the user a question

ASKING FOR JDBC AND SQL92 COMPATIBILITY

ASKING FOR JDBC AND SQL92 COMPATIBILITY

ASKING FOR JDBC AND SQL92 COMPATIBILITY

Monday, March 19, 2012

Ascending Date Column Statistics "dbcc traceon( 2389 )"

Wednesday, March 7, 2012

AS hangs when running a complex mdx query

AS hangs when running a complex mdx querie

sql nulls slow

Blog Archive

About Me

About Me

Followers

Blog Archive