2

So I have a ton of transaction logs in pipe-separated format spread across a few dozen files. Concatenated into one file, it's around 2.4 million lines.

Each transaction has a store number and a company name and ~30 fields total. Right now, I have all 2.4m records in ONE table with no indexes. A simple query takes like 30 seconds to run and it's honestly unacceptable to me.

The thing is, there are no unique pieces of data that I can create primary keys with. Each store can have multiple transactions and each transaction number can have multiple lines (multiple products, returns, etc). There are 300+ stores and so a table for each store doesn't really seem realistic since I would have to join all of these just to get the data I need. What can I do?

  • Have a look into table partitioning. You could split the tables by month on the transaction date. Your DBMS should be able to handle this for you and will greatly improve your query performance. – Ben Oastler Nov 12 '14 at 3:41
  • I agree with Ben that table partioning could help, but as the OP doesnt even have any indexes. Personally i'd start with that first. – Sir Swears-a-lot Nov 12 '14 at 3:56
  • How fast is fast enough? 10 seconds? 5? 1? – Sir Swears-a-lot Nov 12 '14 at 3:57
  • SPLUNK's (www.splunk.com) pretty good at reading file-based stuff and making sense of it. – Michael Green Nov 13 '14 at 2:10
1

Indexes don't have to be unique. Primary keys do.

The purpose of a Primary Key is to uniquely identify a single row of data. If you don't have something that is naturally unique then add a column such as an identity column and define it as the primary key. It's standard practice, and will help you later if you need to update a row.

If the table is being queried for analysis or reporting a single table is fine. Do some analysis on the types of queries you are performing and add indexes on relevant key columns. Good indexes will significantly improve query performance.

For example if you are looking for results for a single store, by adding an index on the store id, you could exclude the other 299 stores from the select. This reduces IO and speeds up the query. If you have years worth of data but are only looking for things that happened in the last week then adding an index to a date column may be a big help.

Look at your queries and see if there fields you are regularly filtering on. Start with those.

Ensure the db has updated statistics for the table and see if performance improves.

The results you get will vary depending upon what you are trying to do. If you are trying to aggregate records (i.e total sales for each store) then the query may still have to read the whole table.

Below I've added a script with a basic test example.

USE [YOURDBNAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

-- Create Test Table. 

CREATE TABLE [dbo].[rewards](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fname] [varchar](50) NULL,
    [lname] [varchar](50) NULL,
    [tstamp] [datetime] NULL,
    [card_id] [int] NULL

) ON [PRIMARY] 

-- Add Primary key. This can be done at table creation. Or added after as shown below.

ALTER TABLE rewards ADD CONSTRAINT pk_rewards PRIMARY KEY NONCLUSTERED  ([id])

-- Add an index on columns that your query will use.

CREATE CLUSTERED INDEX [IDX01] ON [dbo].[rewards]
(   [card_id] ASC,
    [fname] ASC,
    [lname] ASC 
) ON [PRIMARY]

-- Create some test data. 

INSERT INTO [dbo].[rewards] VALUES ('Allan2', 'Aadvark2', getdate(), 2345) ;
INSERT INTO [dbo].[rewards] VALUES ('Billy2', 'Babo0n', getdate(), 2356) ;
INSERT INTO [dbo].[rewards] VALUES ('Chester2', 'Cheetah', getdate(), 2367) ;
INSERT INTO [dbo].[rewards] VALUES ('Doodoo', 'Dog', getdate(), 2376) ;
INSERT INTO [dbo].[rewards] VALUES ('Esme', 'Elephant', getdate(), 1235) ;
INSERT INTO [dbo].[rewards] VALUES ('Freddy', 'Fox', getdate(), 1239) ;

GO

-- run the following query repeatedly to generate volume. 
INSERT INTO [dbo].[rewards]
           ([fname]
           ,[lname]
           ,[tstamp]
           ,[card_id])     
     select [fname]
           ,[lname]
           ,[tstamp]
           ,[card_id]
           from [dbo].[rewards] ;

GO 

SET STATISTICS TIME ON
GO 

-- Test query. This shows that the card 1234 is use by 2 different customers. 

Select r.card_id, count(*) 
from (
    select card_id, fname, lname
    from [dbo].[rewards] 
    group by card_id, fname, lname ) r

group by r.card_id
having count(*) > 1 ;

GO 

SET STATISTICS TIME OFF

GO 

-- I just tested this with 3,538,944 rows. And I get SQL Server Execution Times:  CPU time = 1904 ms,  elapsed time = 490 ms.
-- On repeated execution I can reliably get this under .5 of a second. 

Your results may vary depending upon machine setup and load, But I think you should resonably expect to get your query down to ~1 sec or less.

  • unfortunately i can't just query for one store b/c an employee can work at more than 1 store if they are short-staffed or something. thank you for your answer though, i will try indexes. – sk099 Nov 12 '14 at 3:57
  • So what are you trying to find? If you can post your table design and queries we may be able to make some suggestions. – Sir Swears-a-lot Nov 12 '14 at 4:04
  • basically it's transaction logs for our stores, we have a rewards program where a customer enters/swipes this and gets rewards points for the purchase. employees have been found to swipe their own card, thus getting the rewards points and defrauding the company. the idea is to find records where the same rewards card has been used multiple times with different first_name + last_name pairs. – sk099 Nov 12 '14 at 4:07
  • OK. Shouldn't be a problem. Do you already have a query that gives you the result you want? – Sir Swears-a-lot Nov 12 '14 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.