I have a SQL Server 2012 database that I am using to save data from processed files. We read data from a folder, process it with python and save the results into the database.
One of the first things we do in our ETL process is to check if the file has already been processed. We simply do a:
SELECT id FROM table1 WHERE basename = <basename>
If there is a result we skip the file, if there is no result we process the file. Right now this query takes ~250ms with ~5m records. We have a nonclustered index on the
basename column already.
We will see about 100-200k records added per month. We get the files in batches. So we might see 2k files, and then 2 hours later another 2k files. Some days we will get 10k files, other days we might only get 4k files.
Keeping all other variables the same is there a rule of thumb for projecting when we might run into performance (queries taking longer than 1s) issues with this query other than inserting 15-20 million records into the table and seeing what happens?
CREATE TABLE [dbo].[raw_records]( [id] [int] IDENTITY(1,1) NOT NULL, [basename] [varchar](512) NULL, [filename] [varchar](1024) NULL, [file_size] [int] NULL, [machine] [varchar](10) NULL, [insert_timestamp] [datetime] NULL, [raw_xml] [xml] NULL, [process_status] [varchar](2048) NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE NONCLUSTERED INDEX [basename_index] ON [dbo].[raw_records] ( [basename] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
This table was created long before I started working on it, so I'm assuming someone just made
filename 1024 maximum length to hold "enough". Certainly changeable.
The files that are produced have a timestamp and unique information within the filename itself (e.g.
system1_metadata_timestamp.xml) one "system" couldn't produce (or should never) produce a file with the same timestamp.
select max(len(basename)), max(len(filename)) from dbo.raw_records;
basename - 143,
filename - 168. Probably a good thing to change down to a max of 260.
process_status probably doesn't need to be that long either, but I feel that one is reasonable to guess at, because the column is there to hold error messages from the processing phase. I ran a query on it and had a max of 600 characters. We don't typically query on that column though. It is just more informational for debugging.
I'm going through the application cleaning up stuff such as that. In certain places I can't get away from it but in others can't do much about it unfortunately (e.g. need to actually retrieve the XML column to extract data from it). This question simply stemmed from seeing the performance of the query in question and not wanting it to get away from me. It is the first thing that gets done for every file so if that doesn't work, nothing else will either.