Showing posts with label can039t. Show all posts
Showing posts with label can039t. Show all posts

Monday, March 19, 2012

I can't get this view faster

Hey guys I can't get this view to be faster. It is causing me serious problems. I am trying to get all this data gathered to return to the client but I can't make it any faster. My Low return time is 24 seconds and my High return time is 77 Seconds. But the it will take 24 Seconds even if no data is returned.

What can i do?

I have indexed every foreign key.

i have pictures of my execution path that you can look at at the following address

http://salessimplicity.dnsalias.com/ssnet/needhelp/needhelp.htm

If you need anything to help me let me know I am more than willing to help to get this thing going faster..

The Main view is below

Code Snippet

create VIEW dbo.vuFollowUpTriggerAll AS
SELECT
Client.ID,
SubdivFU.ID AS FollowUpSubdivID,
FollowUpDone.ID AS FollowUpID,
Case
When SubdivFU.DeptID is Not Null then Agent1.ID
Else Agent2.ID
END As SalesAgentID,
-- Agent2.ID AS SalesAgentID,
Subdiv.ID AS SubdivisionID,
Subdiv.MarketingName AS SubdivisionName,
Buyer.FirstName,
Buyer.LastName,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As SalesAgentFirst,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As SalesAgentLast,
-- Agent2.FirstName AS SalesAgentFirst,
-- Agent2.LastName AS SalesAgentLast,
FollowUpDone.DateItemEntered,
FollowUpDone.DateItemComplete AS DateComplete,
FollowUpDone.Custom,
--Removed ClientStatus.Status - didn't see it used in code.
SubdivFU.ContactDesc AS TaskDesc,
FollowUpDone.Removed,
--For Contingencies and Deposit Reminders, we subtract the days rather then add them.
DATEADD(Day, TE.Multiplier * ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate) AS DueDate,
ClientDate.DepositID,
SubdivFU.ContactType AS TaskType,
SubdivFU.MasterReportID,
SubdivFU.EmailLetterID,
Buyer.Email,
Buyer.DoNotSendEmail,
Buyer.DoNotSendLetter,
SubdivFU.OverrideOptOut,
FollowUpDone.UserID,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As AssignedToFirstName,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As AssignedToLastName
FROM
dbo.tbSubdivision Subdiv
INNER JOIN dbo.tbSubdivisionFollowUp SubdivFU ON SubdivFU.SubdivisionID = Subdiv.ID
-- tbTrigger Event is the table that helps us put this whole spaghetti together
--it joins the Event type to the Date Type to the Multiplier.
INNER JOIN dbo.tbTriggerEvent TE ON TE.ID = SubdivFU.TriggerEvent
--tbDateType is what connects vuClient Dates the appropriate date field.
-- INNER JOIN dbo.tbDateType DT ON DT.ID = TE.DateTypeID
INNER JOIN dbo.tbClient Client ON Subdiv.ID = Client.SubdivisionID
AND ((SubdivFU.ClientRankID = Client.ClientRankID AND TE.UseRank = 1)
OR (TE.UseRank = 0))
--vuCLientDates replaces what we were going to use as tbClientDates but it is almost as fast to generate this on the fly
--as it is to generate it on the fly.
INNER JOIN dbo.vuClientDates ClientDate ON ClientDate.DateTypeID = TE.DateTypeID and Client.ID = ClientDate.ClientID
INNER JOIN dbo.tbMember Member ON Member.ClientID = Client.ID
AND Member.MemberTypeID = 1 --Buyer Type
INNER JOIN dbo.tbIndividual Buyer ON Member.IndividualID = Buyer.ID
--vuFollowUpTriggerGetAgent is a view we use to connect Clients to their Agents in each Department.
Left Outer Join vuFollowUpTriggerGetAgent GetAgent on GetAgent.DeptID = SubdivFU.DeptID and Client.ID = GetAgent.ClientID and GetAgent.SubdivisionID = Subdiv.ID
Left Outer JOIN dbo.tbUser Agent1 ON Agent1.ID = GetAgent.UserID
Inner JOIN dbo.tbUser Agent2 ON Agent2.ID = Client.UserID
LEFT OUTER JOIN dbo.tbFollowUp FollowUpDone ON FollowUpDone.SubdivFollowUpID = SubdivFU.ID
AND FollowUpDone.ClientID = Client.ID
AND isNull(FollowUpDone.DepositID, 0) = isNull(ClientDate.DepositID, 0)
WHERE (FollowUpDone.DateItemComplete Is Null)
AND (isnull(FollowUpDone.Removed,0) = 0)
AND (SubdivFU.Active = 1)
AND ((SubdivFU.TriggerEvent = 'R' AND DATEDIFF(DAY, ClientDate.EventDate, GETDATE()) < 0 )
OR (SubdivFU.TriggerEvent = 'O' AND (FLOOR(CAST(GETDATE() AS float)) >= DATEADD(Day, ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate)) )
OR (SubdivFU.TriggerEvent <> 'R' and SubdivFU.TriggerEvent <> 'O')
)
AND (SubdivFU.OverrideOptOut = 1
OR ((SubdivFU.ContactType = 'O')
OR (SubdivFU.ContactType = 'L' AND Buyer.DoNotSendLetter = 0)
OR (SubdivFU.ContactType = 'E' AND Buyer.DoNotSendEmail = 0)
OR (SubdivFU.ContactType = 'C' AND Buyer.DoNotCall = 0)))
AND (((SubdivFU.DeptID IS Not Null) and (Agent1.ID is Not Null)) or (SubdivFU.DeptID is Null) )

It joins to a few other views defined below

Code Snippet

ALTER View dbo.vuFollowUpTriggerGetAgent AS
-- 5/14/07 DebP Added link to tbDept in order to exclude inactive departments.

SELECT SFU.DeptID, tbClient.ID AS ClientID,
ISNULL(tbClientDept.UserID, ISNULL(tbSubdivisionDept.UserID, Null)) AS UserID,
tbSubdivision.ID as SubdivisionID
FROM tbSubdivision
INNER JOIN dbo.tbClient ON tbClient.SubdivisionID = tbSubdivision.ID
INNER Join
(SELECT SubdivisionID, DeptID
FROM dbo.tbSubdivisionFollowUp
INNER JOIN dbo.tbDept ON dbo.tbSubdivisionFollowUp.DeptID = dbo.tbDept.ID
AND dbo.tbDept.Active <> 0
GROUP BY SubdivisionID, DeptID)
SFU ON SFU.SubdivisionID = tbSubdivision.ID
-- The following is no longer needed since we're doing an inner join to tbDept.
--AND SFU.DeptID is Not Null
LEFT OUTER JOIN dbo.tbSubdivisionDept ON tbSubdivisionDept.DeptID = SFU.DeptID
AND tbSubdivisionDept.SubdivisionID = tbSubdivision.ID
AND tbSubdivisionDept.UserID is not Null
LEFT OUTER JOIN dbo.tbClientDept ON tbClientDept.DeptID = SFU.DeptID
AND tbClient.ID = tbClientDept.ClientID
AND tbClientDept.UserID is not Null

and

Code Snippet

ALTER view dbo.vuClientDates as

SELECT tbClient.ID as ClientID,
Case
When tbClient.ClientStatusID = 4 then 1
When tbClient.ClientStatusID = 1 then 2
When tbClient.ClientStatusID = 2 then 4
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then 11
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then 5
End as DateTypeID,

Case
When tbClient.ClientStatusID = 4 then VisitDate
When tbClient.ClientStatusID = 1 then ContractDate
When tbClient.ClientStatusID = 2 then ActualCloseDate
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then VisitDate
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then CancellationDate
End
as EventDate,
Null as DepositID, Null as StageID, Null as EscrowStageID From tbClient

UNION ALL

SELECT tbClient.ID as ClientID, 3 as DateTypeID, ExpirationDate as EventDate, Null as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
where (tbClient.Contingency = 1)
AND (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in ( 1, 2, 4)
)


UNION ALL

SELECT tbClient.ID as ClientID, 6 as DateTypeID, DateDue as EventDate, Deposits.ID as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
INNER JOIN dbo.tbDeposits Deposits ON Deposits.ClientID = tbClient.ID
AND Deposits.DateDue IS NOT NULL
AND Deposits.DatePaid IS NULL
Where (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in( 1,2,4)
)

These are the indexes i have added for this

Code Snippet

CREATE INDEX [IX_tbIndividual] on [dbo].[tbIndividual] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient] on [dbo].[tbClient] ([ClientStatusID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_1] on [dbo].[tbClient] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_2] on [dbo].[tbClient] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember] on [dbo].[tbMember] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_1] on [dbo].[tbMember] ([IndividualID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_2] on [dbo].[tbMember] ([MemberTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbDeposits] on [dbo].[tbDeposits] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_2] on [dbo].[tbSubdivisionFollowUp] ([TriggerEvent]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp] on [dbo].[tbSubdivisionFollowUp] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_1] on [dbo].[tbSubdivisionFollowUp] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_3] on [dbo].[tbSubdivisionFollowUp] ([ContactType]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_2] on [dbo].[tbFollowUp] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp] on [dbo].[tbFollowUp] ([SubdivFollowUpID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_1] on [dbo].[tbFollowUp] ([MasterReportID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_3] on [dbo].[tbFollowUp] ([DepositID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionDept] on [dbo].[tbSubdivisionDept] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision] on [dbo].[tbUserSubdivision] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision_1] on [dbo].[tbUserSubdivision] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClientDept_1] on [dbo].[tbClientDept] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent] on [dbo].[tbTriggerEvent] ([DateTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent_1] on [dbo].[tbTriggerEvent] ([TriggerEventDesc]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([AvailablePlanOptionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([RoomPlanID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([Username]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_LoginID] on [dbo].[tbUser] ([LoginID]) WITH FILLFACTOR = 95


Hmm... Three table scans (at 25% of the execution plan each) seem to indicate you're missing appropriate indexes somewhere.|||Ok but where?
|||

You have a lot of joins in your query. Have you tried using temporary tables instead to store intermediate results and then join them ?

|||

Well, two of them are on the tbClient with CancelationDate and ClientStatusID, and one of those also looks at Contingency.

(Those are tables 2 and 3 with the 25% costs on the far right of the plan).

You're only indexing on ClientStatusID

You're also using a lot of functions in the filtering which will pop the plan back to table scans.

You may want to develop and "grow" this incrementally. Just do the first two tables and get them honed in, then add in the next table.

Get a good clean manageable baseline to grow from.

|||Also, to go along with Dale, you'll want to create indexes based on the composite key of the join. Don't just create an index for each column individually. Do as he says and "grow" the view. Each time you add something new, review the execution plan again to ensure nothing has snuck up on you.|||

These are all great ideas...

Smile
Thanks

|||How do i get rid of a hash match left?

I can't find the setting.

Hi, I'm completely new to SQL Server and ASP.NET, so please bear with me on my learning curve! I've installed Visual Web Developer Express and SQL Server Express (on the same PC). I then used Web Developer Express to successfully create a new database with a couple of tables in it. So far so good. I then installed SQL Server Management Studio Express on the same PC and tried to connect to my new database. The connection to the SQL Server instance appears to work OK (using Windows Authentication) but I can't see the database I previously created. All I can see are the System Databases. Does anyone know what the solution might be?

Ensure you are really connected to the same SQL Server instance, you could have multiple instances. Ensure you are connecting using the same credentials, your Windows Authenication account may have different permissions than the account used to by Web Developer Express.

Let me know if this does not help

Peter Saddow

|||

Thank you for the feedback. I eventually found out what the problem was: by default, Web Developer Express creates SQL databases which are visible only to the current User Instance. To disable this, you have to set "User Instance = FALSE" on the database connection string. For example, my original connection string was:

Data Source=.\SQLEXPRESS;AttachDbFilename="S:\My Documents\Visual Studio 2005\WebSites\Db1\App_Data\Database.mdf";Integrated Security=True;User Instance=True

By setting User Instance = False, I can now see the database from SQL Server MSE.

|||I came across the same answer you did the only problem is that I may not find the settings file or some way to edit it.

I can't deploy a Cube

I have installed the client version of Sql Server 2005 on my machine and the server version is Installed on a server of my company (VALLEY).

I have created a Cube with the wizard to test Analysis Services 2005 and see the result after the deployment. The cube works with a database called ROI on the server VALLEY,

But, when I try to deploy the cube, I have this error message:

" the user XXXX don't have permission to modify the object or the object does not exist"

I don't know if I must configure some specific access.

Could you help me please, I search a solution since 2 days with any results.

Betty

Betty,

This is probably one of two things:

- Your impersonation information is not correct, for the AS server

- The user, specified in the impersonation config, is not a member of the SQLSERVER2005MSOLAP... group.

Post back if this still does not fix the issue...

|||

Where can I add the user into the SQLSERVER2005MSOLAP group?

And can you confirm than the impersonation information corresponds to the login and password to connect to the database or not?

I have create a cube with the database of the tutorial and i can't deploy it too so I think it' not a problem with the impersonation information but if I'm wrong, say it.

Betty

|||

You can add the user to the group through the Local Users and Groups menu.

( right click "My computer" -> MANAGE -> expand Local Users and Groups -> click Groups )

The group should be named something like SQL2005OLAPUSERS$<Boxname>$mssqlserver depending on set up. This was OLAP Administrators in SQL 2000, I believe.

The impersonation credentials may or may not be be the same to connect to the DB, but I would recommend trying whatever username/password you are using to connect to Analysis Services through Visual Studio.

Tell me if that helps,

Tristan

|||

Sorry but all that don't resolve my problem

|||I got the same error. Connect to Analysis services. Right click on the analysis services server. Go to security and add yourself using the domain/user id.|||

i have installed the client version of Sql Server 2005 on my machine and the server version is Installed on a server of my company.

But, when I try to deploy the cube, I have this error message:

Error 1 The project could not be deployed to the "company server name" server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

i dont know is it configaration mistake or the cube creation mistake

Could you help me please

I can't debug my SQL Server project

I'm coding a TVF in C# and the debugger doesn't stop at all. It used to work when I was coding scalar functions, but it stop working since I removed my assembly from the DB and register it manually to test my SQL scripts. I removed the assembly again from my DB and re-deployed it with VS.2005 hoping that will fix it, but it didn't work. Right now I can't debug.

Have anybody had a problem like this before? Any help will be appreciated.

Do you use your TVF indefault test script in your VS project?
|||Yes. The weird thing is that the debugger was working before, and I debugged scalar functions. But since I deployed the assembly manually and then removed it to deployed it from VS.2005, somehow the debugger stopped working.|||Check content of output window, you could find error messages here.|||

Got it. There was a problem with a commented section in query.

Thank you.

I can't connect to my local database.mdf file

I have just upgraded to Microsoft SQL Server 2005 Express Edition with Advanced Services SP2.

Now I can't connect to my local database.mdf file.

When trying to open or doubleclick or expand my .mdf file the following message pops up:

Generating user instances in SQL Server is disabled. Use sp_configure 'user instance enabled' to generate user instances.

What is the solution for this problem?

hi,

by default, User Instances feature is disabled when you install a SQLExpress instance.. the Install Wizard provides an option to enable it at install time, but you can enable it via sp_configure execution as

EXEC sp_configure 'show advanced option', '1'; GO EXEC sp_configure 'user instances enabled','1'; GO RECONFIGURE GO EXEC sp_configure 'show advanced option', '0'; GO

(restart the SQLExpress service)

that can be executed both in SqlCmd.exe (command line tool) or in a SQL Server Management Studio Express query window (or even via an ADO/Ado.Net connection)..

you can have a look at all "configurable" options at http://msdn2.microsoft.com/en-us/library/ms189631.aspx

regards

|||

Hi Andrea.

I have entered your code in the query editor and saved it as SQLQuery5.sql.
SQL server is restarted.

I right clicked in the query editor and hit Execute and get the folloving error:

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The problem is still there?...

|||

Hi again :)

I right clicked in the query editor and hit Execute again after a while. It works now.

Thanks :)

I can't connect to my local database.mdf file

I have just upgraded to Microsoft SQL Server 2005 Express Edition with Advanced Services SP2.

Now I can't connect to my local database.mdf file.

When trying to open or doubleclick or expand my .mdf file the following message pops up:

Generating user instances in SQL Server is disabled. Use sp_configure 'user instance enabled' to generate user instances.

What is the solution for this problem?

hi,

by default, User Instances feature is disabled when you install a SQLExpress instance.. the Install Wizard provides an option to enable it at install time, but you can enable it via sp_configure execution as

EXEC sp_configure 'show advanced option', '1'; GO EXEC sp_configure 'user instances enabled','1'; GO RECONFIGURE GO EXEC sp_configure 'show advanced option', '0'; GO

(restart the SQLExpress service)

that can be executed both in SqlCmd.exe (command line tool) or in a SQL Server Management Studio Express query window (or even via an ADO/Ado.Net connection)..

you can have a look at all "configurable" options at http://msdn2.microsoft.com/en-us/library/ms189631.aspx

regards

|||

Hi Andrea.

I have entered your code in the query editor and saved it as SQLQuery5.sql.
SQL server is restarted.

I right clicked in the query editor and hit Execute and get the folloving error:

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The problem is still there?...

|||

Hi again :)

I right clicked in the query editor and hit Execute again after a while. It works now.

Thanks :)