SQL Server Takes too long to execute a script to create DB and Stored Procedures
Hi Experts !
I Was Instaling an ASP.NET Application wich came with the scripts to set up the SQL Server Backend.
The Script called TimeTracker_CreateDB.sql is a simple script to create the DataBase, create Tables, create stored procedures , constraints and that's about it.
This query took more than 1 hour to run on the server. To check if the problem was on the server i tried it also on MSDE (local server) and results were as bad.
What could be the cause for this huge amount of time for a simple script ?
It would help if you could post part of the script or give us more details.
You could obfuscate table/field names with a search/replace if it's copyrighted stuff.
Another option would be to open the SQL script in Query Analyser to narrow the scope and find out which statement(s) takes so long.
Just execute the script in smaller parts. Select / highlight each chunk in QA and press F5 to execute only the selected part. Do it until you find the statement that takes ages to run.
Maybe the initialisation script also contains some data : reference tables might be populated on a row-by-row mode using insert statements.
Maybe you could speed-up things by activating constraints and building indexes AFTER the tables are populated, if it's no the case in your current script.
reboot the database into single user mode and run it, you might be getting a locking problem
with other processes.
Jay
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Thanks Hilaire for your fast help !
Your Post was very helpfull for future problems of the kind !
But i found the answear just a moment ago !
The problem was not with Sql Server it was in my client machine (WinXP) (that runs Enterprise edition).
I did a remote loggin to the server (Windows 2003) (copied the sql scripts to a file share on the server) and runned the scripts locally with the query analyser wich was installed on the server and it ran super fast.
But the strange thing is that SQL Server Client Tools like Enterprise Edition and Query analyser should pass the whole script to the server at once and not like it was happening isnt' this true ? What could be the cause of this ?
This should only be a problem if
a) you have somthing like TRACING on.
b) you have a very slow network connection
c) the script is loading a LOT of data
otherwise is should be just as fast as "being there"
fyi: how large in terms of bytes is the script that creates the db?
The Script is 42 Kb.
I Don't think the problem was with the network connection since i tried running this scrip in my deskop with MSDE.
About TRACING where can i check this ?
I'll post the scrip maybe the problem coud be in the type of scrip conflicting with something else :
Thanx !
____________________________
The script is this :
___________________________
USE master
CREATE DATABASE [TimeTracker]
GO
exec sp_dboption N'TimeTracker', N'autoclose', N'false'
GO
exec sp_dboption N'TimeTracker', N'bulkcopy', N'false'
GO
exec sp_dboption N'TimeTracker', N'trunc. log', N'false'
GO
exec sp_dboption N'TimeTracker', N'torn page detection', N'false'
GO
exec sp_dboption N'TimeTracker', N'read only', N'false'
GO
exec sp_dboption N'TimeTracker', N'dbo use', N'false'
GO
exec sp_dboption N'TimeTracker', N'single', N'false'
GO
exec sp_dboption N'TimeTracker', N'autoshrink', N'false'
GO
exec sp_dboption N'TimeTracker', N'ANSI null default', N'false'
GO
exec sp_dboption N'TimeTracker', N'recursive triggers', N'false'
GO
exec sp_dboption N'TimeTracker', N'ANSI nulls', N'false'
GO
exec sp_dboption N'TimeTracker', N'concat null yields null', N'false'
GO
exec sp_dboption N'TimeTracker', N'cursor close on commit', N'false'
GO
exec sp_dboption N'TimeTracker', N'default to local cursor', N'false'
GO
exec sp_dboption N'TimeTracker', N'quoted identifier', N'false'
GO
exec sp_dboption N'TimeTracker', N'ANSI warnings', N'false'
GO
exec sp_dboption N'TimeTracker', N'auto create statistics', N'true'
GO
exec sp_dboption N'TimeTracker', N'auto update statistics', N'true'
GO
use [TimeTracker]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntryLog_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_Categories
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EntryLog_ProjectMembers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_EntryLog] DROP CONSTRAINT FK_EntryLog_ProjectMembers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Categories_Projects]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Categories] DROP CONSTRAINT FK_Categories_Projects
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Roles_Projects]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_Roles_Projects
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Users_Roles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Users] DROP CONSTRAINT FK_Users_Roles
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_WorksOn_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_ProjectMembers] DROP CONSTRAINT FK_WorksOn_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Projects_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TT_Projects] DROP CONSTRAINT FK_Projects_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddProjectMember]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddProjectMember]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_AddUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_AddUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_DeleteUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_DeleteUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetManagerProjectCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetManagerProjectCount]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetUserByUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetUserByUserName]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_GetUserDisplayName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_GetUserDisplayName]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListAllProjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListAllProjects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListAllRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListAllRoles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListCategories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListCategoriesByProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListCategoriesByProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListManagers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListManagers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListMembers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListMembers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjectsByIDs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjectsByIDs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListProjectsWithMembership]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListProjectsWithMembership]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListResourceByIDs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListResourceByIDs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntries]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntriesByCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntriesByCategory]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListTimeEntriesByUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListTimeEntriesByUsers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListUserTimeSummary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListUserTimeSummary]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ListUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_ListUsers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateProject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateTimeEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateTimeEntry]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UpdateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UpdateUser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_UserLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TT_UserLogin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Categories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_EntryLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_EntryLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_ProjectMembers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_ProjectMembers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Projects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Projects]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Roles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TT_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TT_Users]
GO
CREATE TABLE [dbo].[TT_Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ProjectID] [int] NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Abbreviation] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EstDuration] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TT_EntryLog] (
[EntryLogID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Duration] [decimal](10, 2) NOT NULL ,
[EntryDate] [smalldatetime] NOT NULL ,
[ProjectID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[CategoryID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TT_ProjectMembers] (
[ProjectID] [int] NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TT_Projects] (
[ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManagerUserID] [int] NULL ,
[EstCompletionDate] [datetime] NULL ,
[EstDuration] [decimal](10, 2) NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TT_Roles] (
[RoleID] [int] NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TT_Users] (
[DisplayName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RoleID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_Categories] WITH NOCHECK ADD
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_ProjectMembers] WITH NOCHECK ADD
CONSTRAINT [PK_ProjectMembers] PRIMARY KEY CLUSTERED
(
[ProjectID],
[UserID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_Roles] WITH NOCHECK ADD
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_EntryLog] ADD
CONSTRAINT [PK_Tasks] PRIMARY KEY NONCLUSTERED
(
[EntryLogID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_ProjectMembers] ADD
CONSTRAINT [IX_Roles] UNIQUE NONCLUSTERED
(
[ProjectID],
[UserID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_Projects] ADD
CONSTRAINT [DF_Projects_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_Projects] PRIMARY KEY NONCLUSTERED
(
[ProjectID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_Users] ADD
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Users] UNIQUE NONCLUSTERED
(
[UserName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT_Categories] ADD
CONSTRAINT [FK_Categories_Projects] FOREIGN KEY
(
[ProjectID]
) REFERENCES [dbo].[TT_Projects] (
[ProjectID]
) ON DELETE CASCADE
GO
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
SET @ProjectID = @@Identity
SET @TempString = @Members
SET @Count = CHARINDEX(',', @TempString)
WHILE @Count > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
SET @Count = CHARINDEX(',', @TempString)
END
INSERT INTO @TempTable VALUES(CAST(@TempString AS int))
INSERT INTO TT_ProjectMembers
SELECT @ProjectID, UserID FROM @TempTable
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
SET @TempString = @Categories
SET @Count = CHARINDEX(';', @TempString)
WHILE @Count > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @CatName = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @Abbrev = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
--Verify that the UserName is not being added to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName)
BEGIN
SELECT
@@Identity AS UserID
END
ELSE
SELECT -2 AS UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_DeleteProject
(
@ProjectID int
)
AS
DELETE FROM
TT_Projects
WHERE
ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_DeleteTimeEntry
(
@EntryLogID int
)
AS
DELETE FROM
TT_EntryLog
WHERE
EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_DeleteUser
(
@UserID int
)
AS
DELETE FROM
TT_Users
WHERE
UserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_GetManagerProjectCount
@UserID int
AS
SELECT COUNT(*) FROM TT_Projects WHERE ManagerUserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListMembers
(
@ProjectID int
)
AS
SELECT
u.UserID, UserName
FROM
TT_ProjectMembers pm
INNER JOIN
TT_Users u
On
u.UserID = pm.UserID
WHERE
ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListCategoriesByProject
(
@ProjectID int
)
AS
SELECT C.CategoryID,
C.Name,
C.Abbreviation AS CategoryShortName,
EstDuration = CAST(Round(C.EstDuration,0) AS int),
ISNULL(Sum(EL.Duration), 0) AS ActualHours
FROM TT_Categories C
LEFT OUTER JOIN TT_EntryLog EL
ON C.CategoryID = EL.CategoryID
WHERE C.ProjectID = @ProjectID
GROUP BY C.CategoryID, C.Name, C.Abbreviation, C.EstDuration
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_GetProject
(
@ProjectID int
)
AS
SELECT
Name, Description, ManagerUserID, EstCompletionDate, EstDuration = CAST(Round(EstDuration,0) AS int)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc TT_GetUserDisplayName
@Username nvarchar(50)
AS
SELECT DisplayName FROM TT_Users
WHERE UserName = @Username
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListAllProjects
AS
SELECT ProjectID,
Name as ProjectName,
Description,
ManagerUserID,
EstCompletionDate,
EstDuration
FROM TT_Projects
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListAllRoles
AS
SELECT
RoleID,
Name
FROM
TT_Roles
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListCategories
(
@ProjectID int
)
AS
SELECT
CategoryID, Name, Abbreviation, CAST(EstDuration AS int) EstDuration
FROM
TT_Categories
WHERE
ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListManagers
AS
SELECT
UserID, UserName, RoleID
FROM
TT_Users
Where
RoleID = 2
OR
RoleID = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListProjects
(
@UserID int,
@RoleID int
)
AS
IF @RoleID = 1
BEGIN
SELECT ProjectID,
Name as ProjectName,
Description,
ManagerUserID,
UserName,
EstCompletionDate,
EstDuration
FROM
TT_Projects
INNER JOIN
TT_Users
ON
ManagerUserID = UserID
END
ELSE IF @RoleID = 2
BEGIN
SELECT ProjectID,
Name as ProjectName,
Description,
ManagerUserID,
UserName,
EstCompletionDate,
EstDuration
FROM
TT_Projects
INNER JOIN
TT_Users
ON
ManagerUserID = UserID
WHERE ManagerUserID = @UserID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListProjectsByIDs
(
@ProjectIDs nvarchar(512),
@UserID int
)
AS
DECLARE @sql nvarchar(1024),
@RoleID int
SELECT @RoleID = RoleID
FROM TT_Users
WHERE UserID = @UserID;
IF (@RoleID = 1)
BEGIN
SET @sql = 'SELECT P.ProjectID,'
SET @sql = @sql + ' P.Name AS ProjectName,'
SET @sql = @sql + ' P.EstCompletionDate,'
SET @sql = @sql + ' P.EstDuration AS EstHours,'
SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'
END
ELSE IF (@RoleID = 2)
BEGIN
SET @sql = 'SELECT P.ProjectID,'
SET @sql = @sql + ' P.Name AS ProjectName,'
SET @sql = @sql + ' P.EstCompletionDate,'
SET @sql = @sql + ' P.EstDuration AS EstHours,'
SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
SET @sql = @sql + ' AND P.ManagerUserID = ' + CAST(@UserID AS nvarchar(20))
SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'
END
ELSE
BEGIN
SET @sql = 'SELECT P.ProjectID,'
SET @sql = @sql + ' P.Name AS ProjectName,'
SET @sql = @sql + ' P.EstCompletionDate,'
SET @sql = @sql + ' P.EstDuration AS EstHours,'
SET @sql = @sql + ' P.EstDuration AS ActualHours '
SET @sql = @sql + 'FROM TT_Projects P WHERE 1=0 '
END
EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListProjectsWithMembership
(
@QueryUserID int,
@UserID int
)
AS
DECLARE @@QueryUserRoleID int
SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID
IF @@QueryUserRoleID = 1 OR @QueryUserID = @UserID
BEGIN
SELECT TT_Projects.ProjectID,
Name,
Description,
ManagerUserID,
EstCompletionDate,
EstDuration
FROM TT_Projects
INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
WHERE UserID = @UserID
END
IF @@QueryUserRoleID = 2
BEGIN
SELECT TT_Projects.ProjectID,
Name,
Description,
ManagerUserID,
EstCompletionDate,
EstDuration
FROM TT_Projects
INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
WHERE UserID = @UserID AND ManagerUserID = @QueryUserID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET @sql = 'SELECT U.UserID, U.UserName, '
SET @sql = @sql + ' Sum(EL.Duration) AS totalHours'
SET @sql = @sql + ' FROM TT_EntryLog EL INNER JOIN TT_Users U'
SET @sql = @sql + ' ON EL.UserID = U.UserID '
SET @sql = @sql + ' WHERE U.UserID IN (' + @UserIDs + ') '
SET @sql = @sql + ' AND EL.EntryDate >= ' +CAST(@StartDate as nvarchar(20))+ ' AND EL.EntryDate <= ' + CAST(@EndDate as nvarchar(20))
SET @sql = @sql + ' GROUP BY U.UserID, U.UserName'
EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListTimeEntries
(
@QueryUserID int,
@UserID int,
@StartDate datetime,
@EndDate datetime
)
AS
DECLARE @@QueryUserRoleID int
SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID
IF @@QueryUserRoleID = 1 or @QueryUserID = @UserID
BEGIN
SELECT
EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
ManagerUserID, TT_Categories.Abbreviation AS CatShortName
FROM
TT_EntryLog
INNER JOIN
TT_Categories
ON
TT_EntryLog.CategoryID = TT_Categories.CategoryID
INNER JOIN
TT_Projects
ON
TT_EntryLog.ProjectID = TT_Projects.ProjectID
WHERE
UserID = @UserID
AND
Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
AND
Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
END
ELSE IF @@QueryUserRoleID = 2
BEGIN
SELECT
EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
ManagerUserID, TT_Categories.Abbreviation AS CatShortName
FROM
TT_EntryLog
INNER JOIN
TT_Categories
ON
TT_EntryLog.CategoryID = TT_Categories.CategoryID
INNER JOIN
TT_Projects
ON
TT_EntryLog.ProjectID = TT_Projects.ProjectID
WHERE
UserID = @UserID
AND
Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
AND
Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
AND
ManagerUserID = @QueryUserID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListTimeEntriesByCategory
(
@CategoryID int
)
AS
SELECT
U.UserName,
U.UserID,
temp.MinEntryDate,
temp.MaxEntryDate,
Sum(EL.Duration) AS Duration
FROM TT_EntryLog EL
INNER JOIN TT_Users U
ON EL.UserID = U.UserID
INNER JOIN (SELECT UserID, MIN(EntryDate) AS MinEntryDate, MAX(EntryDate) AS MaxEntryDate FROM TT_EntryLog GROUP BY UserID) AS temp
ON temp.UserID = EL.UserID
WHERE EL.CategoryID = @CategoryID
GROUP BY U.UserName, U.UserID, temp.MinEntryDate, temp.MaxEntryDate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
DECLARE
@sSqlString nvarchar(1024),
@sSubSql nvarchar(1024),
@RoleID int
SELECT @RoleID = RoleID
FROM TT_Users
WHERE UserID = @ManagerUserID;
IF (@RoleID = 1)
BEGIN
SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2 GROUP BY U.UserID, U.UserName'
END
ELSE IF (@RoleID = 2)
BEGIN
SET @sSubSql = 'SELECT PM.UserID FROM TT_Projects P INNER JOIN TT_ProjectMembers PM'
SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')'
SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From TT_Projects Where ManagerUserID = @3) '
SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2 GROUP BY U.UserID, U.UserName'
END
ELSE
SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From TT_Users U Where 1=0'
EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TT_ListUsers
(
@UserID int,
@RoleID int
)
AS
IF @RoleID = 1
BEGIN
SELECT
UserID,
UserName,
TT_Users.RoleID,
TT_Roles.Name 'RoleName'
FROM
TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
END
ELSE IF @RoleID = 2
BEGIN
SELECT DISTINCT
TT_Users.UserID,
TT_Users.UserName,
TT_Users.RoleID,
TT_Roles.Name 'RoleName'
FROM TT_Users
INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
WHERE @UserID = TT_Projects.ManagerUserID OR @UserID = TT_Users.UserID
END
ELSE
SELECT
UserID,
UserName,
TT_Users.RoleID,
TT_Roles.Name 'RoleName'
FROM
TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
WHERE UserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
DECLARE @Error int
DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
DECLARE @TempString varchar(2000)
DECLARE @Temp varchar(4000)
DECLARE @Count int
DECLARE @TempCatTable TABLE(CategoryID int primary key)
DECLARE @InnerTemp nvarchar(50)
DECLARE @CatID int
DECLARE @CatName varchar(20)
DECLARE @Abbrev varchar(5)
DECLARE @Duration decimal(10,2)
DECLARE @InnerCount int
BEGIN TRANSACTION
-- Update the Project
UPDATE
TT_Projects
SET
Name=@Name,
Description = @Description,
ManagerUserID = @ManagerUserID,
EstCompletionDate = @EstCompletionDate,
EstDuration = @EstDuration
WHERE
ProjectID = @ProjectID
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
SET @TempString = @SelectedMembers
SET @Count = CHARINDEX(',', @TempString)
WHILE @Count > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
SET @Count = CHARINDEX(',', @TempString)
END
INSERT INTO @TempTable VALUES(CAST(@TempString AS int))
DELETE
TT_ProjectMembers
WHERE
ProjectID = @ProjectID
AND UserID NOT IN(SELECT UserID FROM @TempTable)
INSERT INTO TT_ProjectMembers
SELECT @ProjectID, UserID FROM @TempTable WHERE UserID NOT IN
(SELECT UserID FROM TT_ProjectMembers WHERE ProjectID = @ProjectID)
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
SET @TempString = @Categories
SET @Count = CHARINDEX(';', @TempString)
WHILE @Count > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @CatID = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @CatName = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @Abbrev = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @Duration = CAST(Round(@Temp,0) AS int)
IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
BEGIN
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
SELECT @CatID = @@IDENTITY
END
else
BEGIN
UPDATE
TT_Categories
SET
[Name] = @CatName,
Abbreviation = @Abbrev,
EstDuration = @Duration
WHERE
ProjectID = @ProjectID
AND
CategoryID = @CatID
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
END
INSERT INTO @TempCatTable VALUES(@CatID)
SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
SET @Count = CHARINDEX(';', @TempString)
END
set @Temp = @TempString
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @CatID = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @CatName = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @Abbrev = @InnerTemp
SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)
SET @Duration = CAST(Round(@Temp,0) AS int)
IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
BEGIN
INSERT INTO TT_Categories
(
ProjectID,
[Name],
Abbreviation,
EstDuration
)
VALUES
(
@ProjectID,
@CatName,
@Abbrev,
@Duration
)
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
SELECT @CatID = @@IDENTITY
END
ELSE
BEGIN
UPDATE
TT_Categories
SET
[Name] = @CatName,
Abbreviation = @Abbrev,
EstDuration = @Duration
WHERE
ProjectID = @ProjectID
AND
CategoryID = @CatID
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
END
INSERT INTO @TempCatTable VALUES(@CatID)
DELETE
TT_Categories
WHERE
ProjectID = @ProjectID
AND
CategoryID NOT IN(SELECT CategoryID FROM @TempCatTable)
SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER
COMMIT TRANSACTION
RETURN 0
ERROR_HANDLER:
IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
RETURN @Error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Verify that the UserName is not being updated to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName AND UserID<>@UserID)
BEGIN
UPDATE
TT_Users
SET UserName=@UserName,
Password = @Password,
DisplayName = @DisplayName,
-- FirstName=@FirstName,
-- LastName=@LastName,
-- Email = @Email,
-- Telephone = @Telephone,
RoleID = @RoleID
WHERE
UserID=@UserID
IF (@@Error<>0) GOTO ErrorHandler
SELECT
1 AS retval
END
-- If UserName is not unique, indicate so with -2
ELSE
SELECT
-2 AS retval
IF @@Error<>0
GOTO ErrorHandler
ELSE
BEGIN
-- COMMIT TRANSACTION
RETURN (0)
END
ErrorHandler:
BEGIN
-- ROLLBACK TRANSACTION
RETURN (1)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure TT_UserLogin
(
@UserName nvarchar(100),
@Password nvarchar(50)
)
AS
SELECT
UserName
FROM
TT_Users
WHERE
UserName = @UserName
AND
Password = @Password
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Since you found a solution by yourself,
you can post a free question in the Community Support area,
to get this question closed and your points refunded.
The problem was caused by a running process called run32dll.exe wich was consuming 99% of the CPU resources.
This process started with enterprise manager (I don't know why). The query was being processed very slowly because of the obvious unavailable system resources!!! Dahh ! The instructions were being executed step by step !!
I killed the process, test it again and it all when right :-)
Sory for the inconvenience Folks !
Moral of the story: L.F.O. - Look for the Obvious !
At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.
You could obfuscate table/field names with a search/replace if it's copyrighted stuff.
Another option would be to open the SQL script in Query Analyser to narrow the scope and find out which statement(s) takes so long.
Just execute the script in smaller parts. Select / highlight each chunk in QA and press F5 to execute only the selected part. Do it until you find the statement that takes ages to run.
Maybe the initialisation script also contains some data : reference tables might be populated on a row-by-row mode using insert statements.
Maybe you could speed-up things by activating constraints and building indexes AFTER the tables are populated, if it's no the case in your current script.
Cheers
Hilaire