Database Table Slow

Question

I have two tables in the database: tb_CampaignLead and tb_Feed. Both tables have thousands of rows and have one index each on the primary key. tb_CampaignLead has 20% more data than tb_Feed. Still tb_Feed is 6 times slower than tb_CampaignLead for a SELECT *. Can anyone please tell me why is tb_Feed so slow. The tables structures are below:

CREATE TABLE [dbo].[tb_Feed](
    [FeedID] [uniqueidentifier] NOT NULL,
    [DateCreated] [smalldatetime] NULL,
    [LeadSourceID] [int] NULL,
    [RawLeadURL] [nvarchar](max) NULL,
    [CostPerEnquiry] [money] NULL,
    [ResultText] [nvarchar](max) NULL,
    [SrcResultText] [nvarchar](max) NULL,
    [SrcResult] [bit] NULL,
    [Encrypted] [bit] NULL,
    [ProductID] [int] NULL,
 CONSTRAINT [PK_tb_Feed] PRIMARY KEY CLUSTERED 


CREATE TABLE [dbo].[tb_CampaignLead](
    [LeadID] [uniqueidentifier] NOT NULL,
    [FeedID] [uniqueidentifier] NOT NULL,
    [CampaignID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [CompanyID] [int] NOT NULL,
    [LeadSourceID] [int] NOT NULL,
    [DateCreated] [smalldatetime] NULL,
    [LeadTextEmail] [nvarchar](max) NULL,
    [LeadTextOther] [nvarchar](max) NULL,
    [DateSent] [smalldatetime] NULL,
    [EmailResponse] [nvarchar](max) NULL,
    [OtherResponse] [nvarchar](max) NULL,
    [EmailOK] [bit] NULL,
    [OtherOK] [bit] NULL,
    [ResultPass] [bit] NULL,
    [L_Title] [nvarchar](50) NULL,
    [L_Email] [nvarchar](100) NULL,
    [L_Firstname] [nvarchar](max) NULL,
    [L_Surname] [nvarchar](max) NULL,
    [L_Address1] [nvarchar](max) NULL,
    [L_Address2] [nvarchar](max) NULL,
    [L_Address3] [nvarchar](max) NULL,
    [L_TownCity] [nvarchar](max) NULL,
    [L_AreaRegion] [nvarchar](max) NULL,
    [L_Country] [nvarchar](max) NULL,
    [L_PostCode] [nvarchar](50) NULL,
    [L_Telephone1] [nvarchar](50) NULL,
    [L_Telephone2] [nvarchar](50) NULL,
    [L_DOB] [smalldatetime] NULL,
    [L_Extra1] [nvarchar](max) NULL,
    [L_Extra2] [nvarchar](max) NULL,
    [L_Extra3] [nvarchar](max) NULL,
    [L_Extra4] [nvarchar](max) NULL,
    [L_Extra5] [nvarchar](max) NULL,
    [L_Extra6] [nvarchar](max) NULL,
    [L_Extra7] [nvarchar](max) NULL,
    [L_Extra8] [nvarchar](max) NULL,
    [L_Extra9] [nvarchar](max) NULL,
    [L_Extra10] [nvarchar](max) NULL,
    [L_Extra11] [nvarchar](max) NULL,
    [L_Extra12] [nvarchar](max) NULL,
    [L_Extra13] [nvarchar](max) NULL,
    [L_Extra14] [nvarchar](max) NULL,
    [L_Extra15] [nvarchar](max) NULL,
    [L_Extra16] [nvarchar](max) NULL,
    [L_Extra17] [nvarchar](max) NULL,
    [L_Extra18] [nvarchar](max) NULL,
    [L_Extra19] [nvarchar](max) NULL,
    [L_Extra20] [nvarchar](max) NULL,
    [SourceCost] [money] NULL,
    [CampaignCost] [money] NULL,
    [DeliveredPass] [bit] NULL,
    [FieldReqBWAND] [int] NULL,
    [FieldSuppliedBWAND] [int] NULL,
    [FilterBWAND] [int] NULL,
    [FilterPassBWAND] [int] NULL,
    [OPFilterBWAND] [int] NULL,
    [OPFilterPassBWAND] [int] NULL,
    [ProcessBWAND] [int] NULL,
    [ProcessPassBWAND] [int] NULL,
    [L_MobileNetwork] [nvarchar](max) NULL,
    [SrcResultPass] [bit] NULL,
 CONSTRAINT [PK_tb_CampaignLead] PRIMARY KEY CLUSTERED

You'll need to give us more information. For starters, what RDBMS (Looks like SQL Server)? What is your storage configuration? — squillman, Jun 12 '13 at 14:22
First, how are you executing and measuring `SELECT *` on both of these tables. Describe explicitly what you do to determine this. Secondly, what is the rowcount and the dataspace (not including indexes) of these two tables? You can get the table information in Management Studio (SSMS) by right-clicking on the database in the Explorer pane, then select Reports..Disk Usage by Table. Look in the `Data(KB)` column and tell us what that is for both tables. — RBarryYoung, Jun 12 '13 at 14:25
@squillman Yes I am using SQL Server 2008 R2. Can you please explain what do you mean by storage configuration. — user2478704, Jun 12 '13 at 19:25
@RBarryYoung I am executing the query from the SQL server management studio. I switch on actual execution plan and client statistics SELECT * from tb_CampaignLead total rows : 463352 total execution time : 63118 Select * from tb_feed total rows : 363387 total execution time : 418337 All queries are run after clearing the cache the dataspace for campaignlead table is 734832 the dataspace for feed table is 2595792 — user2478704, Jun 12 '13 at 19:40

score 2 · Answer 1 · edited May 23 '17 at 12:32

Using this information from your comments:

dataspace for campaignlead table is 734,832 the dataspace for feed table is 2,595,792

As you can see, despite having fewer rows, the total amount of data for tb_Feed is over 3.5 times as large as for tb_CampaignLead. When you do SELECT * From ... in SSMS, it effectively has to retrieve that much data, format it, push it through the network connection (if you're running from a client), and then render it into the output grid.

For small datasets, this is so fast that it is not a significant part of the query execution time. Plus, it can be buffered to make it appear even faster. However, for large datasets this can take a considerable amount of time and the output buffers/pipes will begin to back-up. This can not only slow down the query execution time, it can actually become its dominant factor.

So for queries of this size in SSMS, we would actually expect tb_Feed to take around 3.5 times longer than tb_CampaignLead. So, while not exactly the same, your results are certainly in the right ballpark.

There are other things in these tables that can cause the additional difference, including how much of the NVARCHAR(MAX) data is being stored "out-of-column" for each table. Here's another question that has some answers about that: Should I use an inline varchar(max) column or store it in a separate table?

Maybe the database needs to be normalized? Just by looking at "tb_CampaignLead" table, I can see at least 2 or 3 tables that could be made with it. And Indexes (in moderation) are a good thing to have too. — Danielle Paquette-Harvey, Jun 13 '13 at 13:37
@DaniellePaquette-Harvey Possibly. But if they really want every row of every column in this table, then normalization won't make it any faster. However, IMHO, it seems unlikely that they really would want that normally. — RBarryYoung, Jun 13 '13 at 13:50

Database Table Slow

1 Answers1