Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pashatest
Starting Member
3 Posts |
 Posted - 2013-06-12 : 10:11:36
|
| 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 belowCREATE 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 10:19:04
|
| is it just a SELECT * or do you've some filter conditions as well using WHERE?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
pashatest
Starting Member
3 Posts |
Posted - 2013-06-12 : 10:49:01
|
| Just a SELECT * with no where conditions. Basically for any transaction for tb_feed seems to be much slower than tb_campaignlead. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 11:43:35
|
| what doesn execution plan sugest?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
pashatest
Starting Member
3 Posts |
Posted - 2013-06-12 : 15:42:22
|
| It just says a select of the clustered index scan (the primarykey) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-13 : 00:49:01
|
| is there any other steps that have high cost?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-06-14 : 00:10:49
|
| This is just a guess but I'd say it's likely to be the amount of data you are returning rather than the number of rows. RawLeadURL] [nvarchar](max) NULL,[ResultText] [nvarchar](max) NULL,[SrcResultText] [nvarchar](max) NULL,These guys can store a ton of data, all of which has to be scooped up and sent across the network. Furthermore, depending on how big they are, they could be stored off-table necessitating more IO when they are retrieved. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-20 : 14:29:05
|
| "only select without a where condition", scan would be always going to perform by the SQL server. Perhaps you might want to check the fregmentation and defregment it if required?CheersMIK |
|
|
|
|
|
|
|
|
Database Table Slow