December 23, 2008 at 4:32 pm
Hi all,
This is my first time posting on this forum in hope to get some help from all the sql server experts out there.
We just recently deploy a sql server view to our production website and we have been experiencing timeout problems ever since. However, the timeout problems is very sporadic, but it does seems to get worse while the underlying tables are being updated.
This is how the view works:
It includes INNER JOIN and OUTER JOIN with 4 base tables and very hour we're updating the journal and activity tables.
1. Journal
2. Activity
3. Transmission_Log
4. Transmission_Log_Detail
SELECT
dbo.activity.safe_id, dbo.activity.activity_id, dbo.activity.type_code, dbo.activity.amount,
dbo.activity.timestamp AS activity_datetime, dbo.activity.polled_date,
MAX(dbo.transmission_log.timestamp) AS transmission_timestamp,
MAX(dbo.transmission_log.transmission_log_id) AS transmission_log_id
FROM dbo.transmission_log
INNER JOIN
dbo.transmission_log_detail ON dbo.transmission_log.transmission_log_id = dbo.transmission_log_detail.transmission_log_id
INNER JOIN
dbo.journal ON dbo.transmission_log_detail.activity_id = dbo.journal.journal_id
RIGHT OUTER JOIN
dbo.activity ON dbo.journal.activity_id = dbo.activity.activity_id
GROUP BY dbo.activity.safe_id, dbo.activity.activity_id, dbo.activity.type_code, dbo.activity.amount, dbo.activity.timestamp, dbo.activity.polled_date
My questions and concerns are:
1. When the base tables are being written, is it going to lock the tables causing the view to timeout??
If yes, should i use WITH (NOLOCK) when I create the view??
2. Is the timeout issue causing by the poorly written view??
3. What's really causing the timeout issue??
Thanks in advance!!
December 24, 2008 at 1:25 am
jasmineywchen (12/23/2008)
My questions and concerns are:1. When the base tables are being written, is it going to lock the tables causing the view to timeout??
Probably not the entire table, but portions of it, yes. A view is just a saved select statement, it doesn't store anything
If yes, should i use WITH (NOLOCK) when I create the view??
Not unless you are aware of the pitfalls and are happy with data that may be inconsistent or simply wrong. Nolock essentially says to SQL "I don't mind if my data is not accurate"
2. Is the timeout issue causing by the poorly written view??
Maybe. No easy way to tell. Can you post the schemas of the base tables and the indexes on them? How many rows in the tables, how much is changing at any point, how many does the view return?
3. What's really causing the timeout issue??
No way to tell at this point.
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
December 28, 2008 at 9:42 pm
Thanks for you reply!! Especially during the holiday season! 🙂
Here are the table schema and details for each table
1. Jounral - 2,248,196 rows
CREATE TABLE [dbo].[journal](
[journal_id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[safe_id] [decimal](18, 0) NOT NULL,
[timestamp] [datetime] NOT NULL,
[discriminator] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[reference] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[string_value_0] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[int_value_0] [int] NULL,
[int_value_1] [int] NULL,
[int_value_2] [int] NULL,
[int_value_3] [int] NULL,
[int_value_4] [int] NULL,
[int_value_5] [int] NULL,
[int_value_6] [int] NULL,
[money_value_0] [money] NULL,
[date_value_0] [datetime] NULL,
[decimal_value_18_0_0] [decimal](18, 0) NULL,
[Transmission_Processed] [bit] NULL,
[activity_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_journal] PRIMARY KEY CLUSTERED
(
[journal_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Non-Unique, Non-cluster INDEX
a. [activity_id]
2. Activity - 158,267 rows
CREATE TABLE [dbo].[activity](
[activity_id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[safe_id] [decimal](18, 0) NOT NULL,
[type_code] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[amount] [money] NOT NULL,
[timestamp] [datetime] NOT NULL,
[polled_date] [datetime] NULL,
CONSTRAINT [PK_activity] PRIMARY KEY CLUSTERED
(
[activity_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Non-Unique, Non-cluster INDEX
a. [polled_date]
b. [safe_id]
c. [timestamp]
3. Transmission_Log - 1200 rows
CREATE TABLE [dbo].[transmission_log](
[transmission_log_id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[transmission_id] [decimal](18, 0) NOT NULL,
[timestamp] [datetime] NOT NULL,
CONSTRAINT [PK_transmission_log] PRIMARY KEY CLUSTERED
(
[transmission_log_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Non-Unique, Non-cluster INDEX
a. [timestamp]
4. Transmission_Log_Detail - 651,078 rows
CREATE TABLE [dbo].[transmission_log_detail](
[transmission_log_detail_id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[transmission_log_id] [decimal](18, 0) NOT NULL,
[transmission_id] [decimal](18, 0) NOT NULL,
[safe_id] [decimal](18, 0) NOT NULL,
[activity_id] [decimal](18, 0) NOT NULL,
[business_date] [datetime] NOT NULL,
[processed] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_transmission_log_detail_1] PRIMARY KEY CLUSTERED
(
[transmission_log_detail_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Non-Unique, Non-cluster INDEX
a. [transmission_log_id]
b. [transmission_log_id]
The journal table gets updated every hour with about 50 to couple hundred new rows where the activiy table will create about 10~20 new rows. Transmission_Log and transmission_log_detail tables only get updated once per day with 3 new records and couple hundred records respectively.
The view's result could range from 3 to couple hundred rows depending on the criteria you select (safe_id, polled_date). But what I've noticed is not only the website is timing out frequently, but the view in sql analyzer is running slow sometimes. I just ran a query for almost a minute and half but the view only returns 65 rows.
I have a feeling that the problem is with the view itself......but I don't know how I could make the view run any faster. Or should I not use the view after all???
Thank you very much for your help! 🙂
December 28, 2008 at 9:45 pm
Correction on the index on transmission_log_detail table..
Non-Unique, Non-cluster INDEX
a. [transmission_log_id]
b. [activity_id] --> this is the same as journal_id in Journal table
December 29, 2008 at 1:37 am
I can see one potential problem right away. Activity_id in the journal table is a varchar(50), but in the query you're joining that to a decimal. That's going to cause conversion problems and prevent index usage. I would suggest changing the view as follows
RIGHT OUTER JOIN
dbo.activity ON CAST(dbo.journal.activity_id AS DECIMAL(18,0)) = dbo.activity.activity_id
I think the exec plan's needed also. On 2000, that's not trivial to get though.
Please run a select * from that view, with the SET SHOWPLAN_ALL ON option before. Run that to the grid, copy the contents of the grid and paste into excel (the idea is that the various pieces of the output go into different Excel cells)
Zip the spreadsheet and attach it to your post.
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
December 29, 2008 at 8:25 am
I'm using 2005 and have attached the execution plan with my reply.
Again....I really appreciate your immediate response! 🙂
December 29, 2008 at 8:38 am
This is the execution plan after I modify the view with decimal conversion on the activity id.
However, a simple view query still took about 2 minutes to execute in sql analyzer. 🙁
December 29, 2008 at 10:35 am
jasmineywchen (12/29/2008)
I'm using 2005 and have attached the execution plan with my reply.
Please post in the correct forum in the future. If you post in the wrong forum, people are going to make replies that are inappropriate to the version you're using.
Since it was in the 2000 forum, I assumed SQL 2000. There's a much easier way in 2005 to produce the plan and it's much, much esasier to read the plans. Run the query with the exec plan on (from management studio) Right click the plan, select save as. Save as a .sqlplan file, zip that and attach it to your post.
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
December 29, 2008 at 11:32 am
Sorry about posting in the wrong forum! I will make sure the post is in the appropriate forum next time.
I've attached the ExecutionPlan.sqlplan with the reply.
Thank you!
December 29, 2008 at 11:48 am
I'll check tomorrow. Am currently reinstalling SQL.
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 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply