July 2, 2011 at 5:33 am
Hi,
I have following two tables:
Users and UserProfile
One user may have many userProfiles.
-----------------------------------------------------------
-- first table Users
CREATE TABLE [dbo].[Users](
[Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ClientId] [int] NULL,
[UserTypeId] [int] NOT NULL,
[RegistrationStatusId] [int] NULL,
[RegistrationModeId] [int] NULL,
[AddedById] [bigint] NULL,
[UpdateById] [bigint] NULL,
[AddedDateTime] [datetime] NULL,
[UpdatedDateTime] [datetime] NULL,
[IsActive] [bit] NULL,
[IsDeleted] [bit] NULL,
[LastLogOn] [datetime] NULL,
[UserProfileId] [int] NULL,
CONSTRAINT [PK_TLUser_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users] WITH NOCHECK ADD CONSTRAINT [FK_SCSFUser_SCSFUserTypes] FOREIGN KEY([UserTypeId])
REFERENCES [dbo].[UserTypes] ([Id])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_SCSFUser_SCSFUserTypes]
GO
ALTER TABLE [dbo].[Users] WITH NOCHECK ADD CONSTRAINT [FK_Users_RegistrationMode] FOREIGN KEY([RegistrationModeId])
REFERENCES [dbo].[RegistrationMode] ([Id])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_RegistrationMode]
GO
ALTER TABLE [dbo].[Users] WITH NOCHECK ADD CONSTRAINT [FK_Users_UserRegistrationStatus] FOREIGN KEY([RegistrationStatusId])
REFERENCES [dbo].[UserRegistrationStatus] ([Id])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_UserRegistrationStatus]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_AddedDateTime_1] DEFAULT (getdate()) FOR [AddedDateTime]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_UpdatedDateTime_1] DEFAULT (getdate()) FOR [UpdatedDateTime]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsActive] DEFAULT ('true') FOR [IsActive]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsDeleted] DEFAULT ('false') FOR [IsDeleted]
GO
-- Indexes
CREATE NONCLUSTERED INDEX [IX_Users_ClientId_RegistrationStatusId_AddedDateTime] ON [dbo].[Users]
(
[RegistrationModeId] ASC,
[RegistrationStatusId] ASC,
[AddedDateTime] ASC,
[ClientId] ASC,
[UserTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Users_UserProfileId] ON [dbo].[Users]
(
[UserProfileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [PK_TLUser_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
----------------------------------------
-- second table UserProfiles
CREATE TABLE [dbo].[UserProfile](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoginId] [nvarchar](100) NULL,
[Password] [nvarchar](100) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[EmailAddress] [nvarchar](200) NULL,
[IsActive] [bit] NULL,
[IsDeleted] [bit] NULL,
[LastLogon] [datetime] NULL,
[Phone] [nvarchar](20) NULL,
[Gender] [bit] NULL,
[Address1] [nvarchar](100) NULL,
[Address2] [nvarchar](100) NULL,
[City] [nvarchar](200) NULL,
[StateId] [int] NULL,
[CountryId] [smallint] NULL,
[Zip] [nvarchar](50) NULL,
[DateOfBirth] [datetime] NULL,
[FaxNumber] [nvarchar](20) NULL,
[CellNumber] [nvarchar](20) NULL,
[AddedById] [bigint] NULL,
[UpdateById] [bigint] NULL,
[AddedDateTime] [datetime] NULL,
[UpdatedDateTime] [datetime] NULL,
[ConfirmationDateTime] [datetime] NULL,
CONSTRAINT [PK_UserProfile] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserProfile] ADD CONSTRAINT [DF_UserProfile_AddedDateTime] DEFAULT (getdate()) FOR [AddedDateTime]
GO
ALTER TABLE [dbo].[UserProfile] ADD CONSTRAINT [DF_UserProfile_UpdatedDateTime] DEFAULT (getdate()) FOR [UpdatedDateTime]
GO
-- indexes
ALTER TABLE [dbo].[UserProfile] ADD CONSTRAINT [PK_UserProfile] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Following is the no. of records in each table./
select count(*) from users -- 306940
select COUNT(*) FROM UserProfile -- 173958
I am running following simple query which is taking min. of 26 seconds and max. (i checked) 3 minutes:
select UP.EmailAddress, UP.LoginId, UP.FirstName, UP.LastName,
U.Id, U.RegistrationStatusId, U.IsActive, U.AddedDateTime, U.IsDeleted from Users u Inner Join UserProfile UP
On UP.Id = U.UserProfileId
Should I keep the user.UserProfileId with NOT NULL check? will this increase performance?
Please help and guide me how do i get performance to return results with max. performance in given environment?
Shamshad Ali
July 2, 2011 at 10:36 am
I must congratulate you on providing table definitions, which in many cases others requesting assistance do not do. However in your instance, if I execute the script for creating the table users the following errors are reported
Msg 1767, Level 16, State 0, Line 1
Foreign key 'FK_Users_RegistrationMode' references invalid table 'dbo.RegistrationMode'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_Users_RegistrationMode' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 1
Foreign key 'FK_Users_UserRegistrationStatus' references invalid table 'dbo.UserRegistrationStatus'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_Users_UserRegistrationStatus' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1779, Level 16, State 0, Line 1
Table 'Users' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
In a similiar fashion, errors are also listed when creating the second table,
if you still require assistance may I suggest that you execute your posted scripts in non production test DB and correct the scripts so that others can execute them without error.
With that said, can you
1. Execute the select procedure, using the data where as you have stated returns results in a max of 26 seconds and when executing save the actual execution plan for posting to this forum.
2. Execute the select procedure, using the data where as you have stated returns results in minutes and save the actual execution plan for posting to this forum.
3. Include in your revised posting some sample data for both the rapid and lengthy exeuction cases.
For instructions as how to save and post an execution plan, click on the second link in my signature block to learn how to do so, quickly and easily.
In this manner I believe some one will be able to assist you with a tested solution.
July 2, 2011 at 10:45 am
Shamshad Ali (7/2/2011)
select count(*) from users -- 306940select COUNT(*) FROM UserProfile -- 173958
I am running following simple query which is taking min. of 26 seconds and max. (i checked) 3 minutes:
select UP.EmailAddress, UP.LoginId, UP.FirstName, UP.LastName,U.Id, U.RegistrationStatusId, U.IsActive, U.AddedDateTime, U.IsDeleted from Users u Inner Join UserProfile UP
On UP.Id = U.UserProfileId
Do you really need to return all users with their matching profiles? That query is going to return hundreds of thousands of rows. What are you going to do with hundreds of thousands of rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2011 at 10:47 am
Shamshad Ali (7/2/2011)
...Following is the no. of records in each table./
select count(*) from users -- 306940
select COUNT(*) FROM UserProfile -- 173958
I am running following simple query which is taking min. of 26 seconds and max. (i checked) 3 minutes:
select UP.EmailAddress, UP.LoginId, UP.FirstName, UP.LastName,U.Id, U.RegistrationStatusId, U.IsActive, U.AddedDateTime, U.IsDeleted from Users u Inner Join UserProfile UP
On UP.Id = U.UserProfileId
Should I keep the user.UserProfileId with NOT NULL check?
Maybe, but my guess is probably not. The nonclustered index on UserProfileId does not cover your query, and even if it did the cardinality may prevent a seek. How many rows have a NULL UserProfileId?
As an aside, are you really returning up to ~307K rows with this query? What could possibly need to see that many rows at one time that would not want to supply something in the WHERE clause?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 3, 2011 at 10:07 am
Yes, I understand the questions, why I am willing to return all rows... stupid i am (it is a common question and probably NOT a good idea to return all data) There is a company which has many of thousands of users (our one client). The user interface shows up to send email alert to selected employess, which fill up a dropdown list with all employees emails with a check box. among those he/she can remove or select emails and send email invitation. The user interface has an option to view 1000, 5000 and all. This is what takes time when selecting "all" option. Our client don't want to wait for 1000 or 5000 option for his company employees when there is an urgent invitation to send immediately. In future the MNC may join other domains from their network (future case - their plan) so we don't know yet how much the figure will go. The client is BOSS and paying, so we have to say
"Yes Sir". in any case. We are load testing for such case before we go on production and say "its ready" ...
sorry for wrong script:
There were many of table columns and for understanding the issue i removed those un-necessary columns due to which the script is failing. sorry for that. For professional who can understand the table structure is enogh. any ways i will try to test the script before posting. once again sorry for the incorrect script.
Shamshad Ali.
July 3, 2011 at 11:12 am
To be honest, if you insist on selecting all rows then you must just accept that it takes time. It takes time to get that volume of data off disk. It takes time to process. It takes time to stream back to the client app.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2011 at 4:59 pm
Hi Shamshad,
Just to rule out that your query isn't taking a long time to run due to it displaying a ton of rows in the output window, can you see how long it takes to run as a SELECT COUNT(*) statement?
Jason
Webmaster at SQL Optimizations School
July 3, 2011 at 5:02 pm
jasonwi1202 (7/3/2011)
Hi Shamshad,Just to rule out that your query isn't taking a long time to run due to it displaying a ton of rows in the output window, can you see how long it takes to run as a SELECT COUNT(*) statement?
Bear in mind that a count may have a completely different access pattern and completely different execution plan to the select that returns columns. If it's faster it won't prove that the time is from transmitting and displaying the data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply