December 22, 2016 at 8:38 am
Hi can someone help me optimize this query
SELECT
[nine_TransactionID]
,[nine_LastUpdate]
,[nine_Data]
FROM [COPS_NO20161219].[dbo].[NineDotHistoric]
WHERE
Substring([nine_data], Charindex(';', [nine_data], 0) + 1, 2) IN
( '51', '52', '53', '54',
'55', '34', '37', '40',
'41', '42', '43', '44',
'45', '46', '47', '48',
'49' )
and Charindex('APPROVED', [nine_data], 0) = 0
The definiton of the table is :
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NineDotHistoric](
[nine_TransactionID] [int] IDENTITY(1,1) NOT NULL,
[nine_LastUpdate] [datetime] NOT NULL,
[nine_Data] [varchar](1000) NULL,
CONSTRAINT [PK__NineDotHistoric__2F10007B] PRIMARY KEY CLUSTERED
(
[nine_TransactionID] DESC
)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].[NineDotHistoric] ADD CONSTRAINT [DF__NineDotHi__nine___2A4B4B5E] DEFAULT ([dbo].[fn_GetDate]()) FOR [nine_LastUpdate]
GO
The table NineDotHistoric have 52 millions records, so when I try to run the select statement is taking to long.
Is there something I can do to resolve that issue?
thanks,
Stan
December 22, 2016 at 9:07 am
The problem here appears to be that you've got too many (more than one, that is) different pieces of information stored in each value of nine_data. Such denormalisation may have seemed a good idea when the database was designed, but now you have 50 million rows, you're seeing why normalisation isn't just one for the purists.
If you don't have the option to redesign the table, you could add a couple of persisted computed columns, one to return the first two characters after the first ";", and another to return 1 if "Approved" is in the value or 0 otherwise. If you changed your query to look at those instead of the nine_data column itself, it would almost certainly go a lot faster. If you put an index on the two new columns, I'd bet it goes blazingly fast.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply