Need better Query !!!!!!!!

Question

Need better Query !!!!!!!!

Bhuvnesh

SSC Guru

Points: 59351
More actions
March 12, 2010 at 12:36 am

#219858

Table SchemaCREATE TABLE [dbo].[AccountExtensionBase](
[AccountId] [uniqueidentifier] NOT NULL,
[New_AccountMaster] [bit] NULL,
[New_AlternatePhone] [nvarchar](100) NULL,
[New_AnnualRevenueRange] [nvarchar](100) NULL,
[New_attire] [nvarchar](100) NULL,
[New_BranchMaster] [bit] NULL,
[New_Categorization] [nvarchar](100) NULL,
[New_chainaffiliation] [int] NULL,
[New_chair] [nvarchar](100) NULL,
[New_cochair] [nvarchar](100) NULL,
[New_CompanyDescription] [nvarchar](100) NULL,
[New_CRMActive] [bit] NULL,
[New_CurrentEventURL] [nvarchar](100) NULL,
[New_dateattendeddemo] [datetime] NULL,
[New_domain] [nvarchar](100) NULL,
[New_DoubleHighRate] [money] NULL,
[New_DoubleLowrate] [money] NULL,
[New_duplicate] [bit] NULL,
[New_EmployeesRange] [nvarchar](100) NULL,
[New_EventCost] [nvarchar](100) NULL,
[New_EventCountry] [nvarchar](100) NULL,
[New_EventDate] [nvarchar](100) NULL,
[New_eventlocation] [nvarchar](100) NULL,
[New_eventname] [nvarchar](100) NULL,
[New_EventsinPastYear] [int] NULL,
[New_EventsYear] [nvarchar](100) NULL,
[New_EventTime] [nvarchar](100) NULL,
[New_eventtype] [nvarchar](100) NULL,
[New_excludefrompromos] [bit] NULL,
[New_fortunex] [bit] NULL,
[New_GuestRooms] [int] NULL,
[New_HashCode] [nvarchar](100) NULL,
[New_honoring] [nvarchar](100) NULL,
[New_hotel] [bit] NULL,
[New_ImportedFrom] [nvarchar](100) NULL,
[New_Industry1] [nvarchar](100) NULL,
[New_Industry2] [nvarchar](100) NULL,
[New_Industry3] [nvarchar](100) NULL,
[New_lastmailed] [datetime] NULL,
[New_lastmessagetype] [int] NULL,
[New_latitude] [float] NULL,
[New_Location] [nvarchar](100) NULL,
[New_longitude] [float] NULL,
[New_MeetingRooms] [int] NULL,
[New_meetingsspace] [nvarchar](255) NULL,
[New_MetroArea] [nvarchar](100) NULL,
[New_organizationtype] [int] NULL,
[New_ProfileURL] [nvarchar](100) NULL,
[New_publicorprivate] [int] NULL,
[New_RegisterationFee] [money] NULL,
[New_Registrants] [int] NULL,
[New_resultcode] [nvarchar](100) NULL,
[New_revenue] [nvarchar](100) NULL,
[New_SalesFax] [nvarchar](100) NULL,
[New_SalesPhone] [nvarchar](100) NULL,
[New_Scale] [nvarchar](100) NULL,
[New_servicetype] [int] NULL,
[New_SingleHighRate] [money] NULL,
[New_SingleLowRate] [money] NULL,
[New_speaker] [nvarchar](100) NULL,
[New_StaffLink] [nvarchar](100) NULL,
[New_STR] [int] NULL,
[New_SubIndustry1] [nvarchar](100) NULL,
[New_SubIndustry2] [nvarchar](100) NULL,
[New_SubIndustry3] [nvarchar](100) NULL,
[New_SurveyAnalysisReport] [nvarchar](100) NULL,
[New_SurveyContact] [nvarchar](100) NULL,
[New_Surveylink] [nvarchar](100) NULL,
[New_SurveyName] [nvarchar](100) NULL,
[New_SurveyProvider] [int] NULL,
[New_Tollfree] [nvarchar](100) NULL,
[New_tradeshow] [bit] NULL,
[New_UpcomingEvents] [int] NULL,
[New_VenueCity] [nvarchar](100) NULL,
[New_VenueHotel] [nvarchar](100) NULL,
[New_VenueState] [nvarchar](100) NULL,
[New_VenueType] [int] NULL,
[New_VenueZipCode] [nvarchar](100) NULL,
[new_importtrackingid] [uniqueidentifier] NULL,
[New_NumberofEmployees] [nvarchar](100) NULL,
[New_currentregisterationprocess] [nvarchar](255) NULL,
[New_MeetingPlace] [nvarchar](255) NULL,
[New_Profile_URL] [nvarchar](255) NULL,
[New_CSNMetroArea] [nvarchar](100) NULL,
[New_VenueCode] [nvarchar](40) NULL,
[New_ExhibitSpace] [nvarchar](40) NULL,
[New_largestMeetingSpace] [nvarchar](40) NULL,
[New_VenueNote] [ntext] NULL,
[New_sleep1] [nvarchar](40) NULL,
[New_venuetypeE1] [nvarchar](100) NULL,
[New_venuenameE1] [nvarchar](360) NULL,
[New_venuelocE1] [nvarchar](360) NULL,
[New_zipE1] [nvarchar](40) NULL,
[New_eventdateE1] [nvarchar](100) NULL,
[New_sleep2] [nvarchar](40) NULL,
[New_venuetypeE2] [nvarchar](100) NULL,
[New_venuenameE2] [nvarchar](360) NULL,
[New_venuelocE2] [nvarchar](360) NULL,
[New_zipE2] [nvarchar](40) NULL,
[New_eventdateE2] [nvarchar](100) NULL,
[New_sleep3] [nvarchar](40) NULL,
[New_VenuetypeE3] [nvarchar](100) NULL,
[New_VenueNameE3] [nvarchar](360) NULL,
[New_venuelocE3] [nvarchar](360) NULL,
[New_zipE3] [nvarchar](100) NULL,
[New_EventDateE3] [nvarchar](100) NULL,
[New_VenueTypee4] [nvarchar](40) NULL,
[New_VenueNameE4] [nvarchar](360) NULL,
[New_venuelocE4] [nvarchar](360) NULL,
[New_ZIpE4] [nvarchar](100) NULL,
[New_EventDateE4] [nvarchar](100) NULL,
[New_sleep5] [nvarchar](40) NULL,
[New_VenueTypeE5] [nvarchar](40) NULL,
[New_VenuenameE5] [nvarchar](360) NULL,
[New_venuelocE5] [nvarchar](360) NULL,
[New_ZipE5] [nvarchar](100) NULL,
[New_EventDateE5] [nvarchar](100) NULL,
[New_sleep4] [nvarchar](100) NULL,
[New_PA_CAtegory] [nvarchar](360) NULL,
CONSTRAINT [PK_AccountExtensionBase] PRIMARY KEY CLUSTERED
(
[AccountId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD CONSTRAINT [New_importtracking_Accounts] FOREIGN KEY([new_importtrackingid])
REFERENCES [dbo].[New_importtrackingBase] ([New_importtrackingId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[AccountExtensionBase] CHECK CONSTRAINT [New_importtracking_Accounts]
GO
/****** Object: Table [dbo].[ContactExtensionBase] Script Date: 03/12/2010 02:12:45 ******/
GO
CREATE TABLE [dbo].[ContactExtensionBase](
[ContactId] [uniqueidentifier] NOT NULL,
[New_Categorization] [nvarchar](100) NULL,
[New_Company] [nvarchar](100) NULL,
[New_ContactType] [int] NULL,
[New_CRMActive] [bit] NULL,
[New_Department1] [nvarchar](100) NULL,
[New_Department2] [nvarchar](100) NULL,
[New_Department3] [nvarchar](100) NULL,
[New_ExcludeFromPromos] [bit] NULL,
[New_HashCode] [nvarchar](100) NULL,
[New_ImportedFrom] [nvarchar](100) NULL,
[New_ImportTracking] [int] NULL,
[New_InvalidDate] [datetime] NULL,
[New_LastCampaign] [nvarchar](50) NULL,
[New_LastMailed] [datetime] NULL,
[New_LastMarketed] [datetime] NULL,
[New_LastMessageType] [int] NULL,
[New_LeadSource] [nvarchar](100) NULL,
[New_Mobile] [nvarchar](100) NULL,
[New_OptOutDate] [datetime] NULL,
[New_Prefix] [nvarchar](100) NULL,
[New_ProductEvent] [bit] NULL,
[New_productmarketing] [bit] NULL,
[New_ProductRFP] [bit] NULL,
[New_ProductSurvey] [bit] NULL,
[New_TitleCode] [nvarchar](100) NULL,
[New_Uploaded] [datetime] NULL,
[New_URL] [nvarchar](350) NULL,
[New_ValidEmail] [bit] NULL,
[New_zip4] [nvarchar](100) NULL,
[new_salesterritoryid] [uniqueidentifier] NULL,
[new_territoryid] [uniqueidentifier] NULL,
[new_importtrackingid] [uniqueidentifier] NULL,
[New_SMM] [bit] NULL,
CONSTRAINT [PK_ContactExtensionBase] PRIMARY KEY CLUSTERED
(
[ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ContactExtensionBase] WITH CHECK ADD CONSTRAINT [FK_ContactExtensionBase_ContactBase] FOREIGN KEY([ContactId])
REFERENCES [dbo].[ContactBase] ([ContactId])
GO
ALTER TABLE [dbo].[ContactExtensionBase] CHECK CONSTRAINT [FK_ContactExtensionBase_ContactBase]
/****** Object: Table [dbo].[ContactBase] Script Date: 03/12/2010 02:11:11 ******/
CREATE TABLE [dbo].[ContactBase](
[ContactId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[DefaultPriceLevelId] [uniqueidentifier] NULL,
[CustomerSizeCode] [int] NULL,
[CustomerTypeCode] [int] NULL,
[PreferredContactMethodCode] [int] NULL,
[LeadSourceCode] [int] NULL,
[DeletionStateCode] [int] NOT NULL,
[OriginatingLeadId] [uniqueidentifier] NULL,
[OwningBusinessUnit] [uniqueidentifier] NULL,
[OwningUser] [uniqueidentifier] NULL,
[PaymentTermsCode] [int] NULL,
[ShippingMethodCode] [int] NULL,
[OwningTeam] [uniqueidentifier] NULL,
[AccountId] [uniqueidentifier] NULL,
[ParticipatesInWorkflow] [bit] NULL CONSTRAINT [Set_To_Zero103] DEFAULT ((0)),
[IsBackofficeCustomer] [bit] NULL,
[Salutation] [nvarchar](100) NULL,
[JobTitle] [nvarchar](100) NULL,
[FirstName] [nvarchar](50) NULL,
[Department] [nvarchar](100) NULL,
[NickName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Suffix] [nvarchar](10) NULL,
[YomiFirstName] [nvarchar](150) NULL,
[FullName] [nvarchar](160) NULL,
[YomiMiddleName] [nvarchar](150) NULL,
[YomiLastName] [nvarchar](150) NULL,
[Anniversary] [datetime] NULL,
[BirthDate] [datetime] NULL,
[GovernmentId] [nvarchar](50) NULL,
[YomiFullName] [nvarchar](450) NULL,
[Description] [ntext] NULL,
[EmployeeId] [nvarchar](50) NULL,
[GenderCode] [int] NULL,
[AnnualIncome] [money] NULL,
[HasChildrenCode] [int] NULL,
[EducationCode] [int] NULL,
[WebSiteUrl] [nvarchar](700) NULL,
[FamilyStatusCode] [int] NULL,
[FtpSiteUrl] [nvarchar](200) NULL,
[EMailAddress1] [nvarchar](100) NULL,
[SpousesName] [nvarchar](100) NULL,
[AssistantName] [nvarchar](100) NULL,
[EMailAddress2] [nvarchar](100) NULL,
[AssistantPhone] [nvarchar](50) NULL,
[EMailAddress3] [nvarchar](100) NULL,
[DoNotPhone] [bit] NULL,
[ManagerName] [nvarchar](100) NULL,
[ManagerPhone] [nvarchar](50) NULL,
[DoNotFax] [bit] NULL,
[DoNotEMail] [bit] NULL,
[DoNotPostalMail] [bit] NULL,
[DoNotBulkEMail] [bit] NULL,
[DoNotBulkPostalMail] [bit] NULL,
[AccountRoleCode] [int] NULL,
[TerritoryCode] [int] NULL,
[IsPrivate] [bit] NULL CONSTRAINT [Set_To_Zero104] DEFAULT ((0)),
[CreditLimit] [money] NULL,
[CreatedOn] [datetime] NULL,
[CreditOnHold] [bit] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedBy] [uniqueidentifier] NULL,
[NumberOfChildren] [int] NULL,
[ChildrensNames] [nvarchar](255) NULL,
[VersionNumber] [timestamp] NULL,
[MobilePhone] [nvarchar](50) NULL,
[Pager] [nvarchar](50) NULL,
[Telephone1] [nvarchar](50) NULL,
[Telephone2] [nvarchar](50) NULL,
[Telephone3] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Aging30] [money] NULL,
[StateCode] [int] NOT NULL,
[Aging60] [money] NULL,
[StatusCode] [int] NULL,
[Aging90] [money] NULL,
[ParentContactId] [uniqueidentifier] NULL,
[SubscriptionId] [uniqueidentifier] NULL,
[PreferredSystemUserId] [uniqueidentifier] NULL,
[PreferredEquipmentId] [uniqueidentifier] NULL,
[LastUsedInCampaign] [datetime] NULL,
[MasterId] [uniqueidentifier] NULL,
[PreferredServiceId] [uniqueidentifier] NULL,
[PreferredAppointmentTimeCode] [int] NULL,
[ExternalUserIdentifier] [nvarchar](50) NULL,
[Merged] [bit] NULL CONSTRAINT [DF_ContactBase_Merged] DEFAULT ((0)),
[PreferredAppointmentDayCode] [int] NULL,
[DoNotSendMM] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotSendMM] DEFAULT ((0)),
CONSTRAINT [cndx_PrimaryKey_Contact] PRIMARY KEY CLUSTERED
(
[ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [account_contacts] FOREIGN KEY([AccountId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [account_contacts]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [business_unit_contacts] FOREIGN KEY([OwningBusinessUnit])
REFERENCES [dbo].[BusinessUnitBase] ([BusinessUnitId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [business_unit_contacts]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [contact_master_contact] FOREIGN KEY([MasterId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_master_contact]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [contact_originating_lead] FOREIGN KEY([OriginatingLeadId])
REFERENCES [dbo].[LeadBase] ([LeadId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_originating_lead]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [contact_owning_user] FOREIGN KEY([OwningUser])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_owning_user]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [contact_parent_contact] FOREIGN KEY([ParentContactId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_parent_contact]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [equipment_contacts] FOREIGN KEY([PreferredEquipmentId])
REFERENCES [dbo].[EquipmentBase] ([EquipmentId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [equipment_contacts]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [price_level_contacts] FOREIGN KEY([DefaultPriceLevelId])
REFERENCES [dbo].[PriceLevelBase] ([PriceLevelId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [price_level_contacts]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [service_contacts] FOREIGN KEY([PreferredServiceId])
REFERENCES [dbo].[ServiceBase] ([ServiceId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [service_contacts]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [system_user_contacts] FOREIGN KEY([PreferredSystemUserId])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [system_user_contacts]
GO
ALTER TABLE [dbo].[ContactBase] WITH NOCHECK ADD CONSTRAINT [team_contacts] FOREIGN KEY([OwningTeam])
REFERENCES [dbo].[TeamBase] ([TeamId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [team_contacts]
Query : select firstname + '|' as 'First Name'
, lastname + '|' as 'L ast Name'
, cb.emailaddress1 + '|' as 'Email Address'
, jobtitle + '|' as 'Title'
, cab.Line1 + '|' as 'Address1_Line1'
, cab.Line2 + '|' as 'Address1_Line2'
, cab.City + '|' as 'City'
, cab.Stateorprovince + '|' as 'State'
, cab.Postalcode + '|' as 'Zip Code'
, cab.Country + '|' as 'Country'
, cab.telephone1 + '|' as 'Phone'
, cab.fax + '|' as 'Fax'
, New_company + '|' as 'Company'
, New_URL + '|' as 'URL'
, New_leadsource + '|' as 'LeadSource'
, New_excludefrompromos as 'Exclude From Promos'
, New_validemail as 'Valid Email'
from contactbase as cb With (nolock)
inner join ContactExtensionBase ceb With (nolock)
on cb.contactid = ceb.contactid
inner join CustomerAddressBase cab With (nolock)
on cb.contactid = cab.ParentId
where
cb.emailaddress1 like '%medtronic.com'
or cb.emailaddress1 like '%ccuseminars.com'
or cb.emailaddress1 like '%natsem.com'
or cb.emailaddress1 like '%asu.edu'
or cb.emailaddress1 like '%asce.org'
or cb.emailaddress1 like '%goldbuyersofamerica.com'
or cb.emailaddress1 like '%relivinc.com'
or cb.emailaddress1 like '%berkeley.edu'
or cb.emailaddress1 like '%post.ca.gov'
or cb.emailaddress1 like '%unm.edu'
or cb.emailaddress1 like '%cta.org'
or cb.emailaddress1 like '%educationfoundation.org'
or cb.emailaddress1 like '%kiewit.com'
or cb.emailaddress1 like '%monavie.com'
or cb.emailaddress1 like '%ndia.org'
or cb.emailaddress1 like '%usc.edu'
or cb.emailaddress1 like '%cfpie.com'
and (new_Excludefrompromos is null OR new_excludefrompromos = '0')
and (new_validemail is null or new_validemail = '1')
this query is taking around 6 minutes but BAD thigs is that i have around 3000 "or cb.emailaddress1 like" filters on it.
i nned to tune it but coudn't .
i have also attahced the plan here
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The problem is the leading wildcards in the where clause. Those are not SARGable and hence a table scan is required.

Consider redesigning the table so that the domain of the email address is stored in a separate column, then you could just use IN or a lookup table to get matches.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

GilaMonster (3/12/2010)
use IN or a lookup table to get matches.

Can you please send me sample query

and why i am gettign key lookup ? , i have made covering index for that.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I don't have time to redesign the table and rewrite for you.

Once you have the domain in a separate column, then you can use straight forward IN to do direct matches instead of LIKE.

So

where

cb.emailaddress_domain IN ('medtronic.com', 'ccuseminars.com' ... )

Assuming that the indexes are correct, that should be faster.

Of course, you need to get the domain into a separate column first.

btw, your table really looks like it needs some designing. It's not normalised.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

thanks i got your point

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

doktor-kermit SSC Veteran Points: 201 More actions · Answer 5

Or you could use fulltext index on the column, or even a soundex function!

LutzM SSC Guru Points: 107049 More actions · Answer 6

i have around 3000 "or cb.emailaddress1 like" filters on it

It's not really a good design to hardcode all those or conditions. Create a separate table holding those values and join the two.

I agree with Gails advice to separate the domain. But if you can't or won't do it, a persisted column with REVERSE(cb.emailaddress1) including an index on it would work as well.

But it would make it harder to see what you're looking for when runnig a query like

WHERE cb.emailaddress1_Reversed like 'moc.aciremafosreyubdlog%' ...

So, Gails advice is the best way to go. But not the only one.. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 7

The design, as has been said, is pretty ordinary.

To avoid many horrible problems in future, I recommend you read the following, carefully:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi