Suggestion on import data

Question

Suggestion on import data

girl_bj

SSCommitted

Points: 1731
More actions
December 16, 2018 at 6:38 pm

#416559

Hi,
Every month I will import data into table. The data doesn't have any primary key. It has year and month for user to pull report monthly. It has more than 20+ columns. I am having issue it takes bout 30 seconds or more to extract from the view.
Any way I can solve this issue? Apply indexing will it help? Need some suggestion here.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Look at the columns you filter and join on. Try indexing those and compare the statistics of your queries before and after...

girl_bj SSCommitted Points: 1731 More actions · Answer 2

The column that I filter is just the year.

Year: 2018

Any sample that I can refer to index those column?

pietlinden SSC Guru Points: 63506 More actions · Answer 3

you need to use a filter like this to be able to use your index:

WHERE [DateField] BETWEEN '01-Jan-2017' AND '31-Dec-2017'

Try indexing your date column and then check the query's execution plan as you write. (I think Grant's book on query plans is on here... download and have read).

girl_bj SSCommitted Points: 1731 More actions · Answer 4

I only can filter according to Year and Month. The data consist of Year, Month, rest of the columns.

Year | Month | 20s+ columns
2018 | Jan | xxx
2018 | Jan | xxx

pietlinden SSC Guru Points: 63506 More actions · Answer 5

So index the Year and Month columns, and then read up on INCLUDE ?

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 6

It would be a lot easier to give you detailed answers with example queries, structure, and execution plans for the existing queries you're running. Otherwise, we're making guesses here.

From an indexing stand point, year and month, may not reduce the amount of data being filtered adequately to see any index, no matter how well constructed, work well. How many rows in the table and how many returned by, say, June 2018 as a filter?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

girl_bj SSCommitted Points: 1731 More actions · Answer 7

girl_bj - Sunday, December 16, 2018 11:18 PM
I only can filter according to Year and Month. The data consist of Year, Month, rest of the columns.
Year | Month | 20s+ columns
2018 | Jan | xxx
2018 | Jan | xxx

data as above. one year data bout 60k+. and the report filter will be just year and month will have option All or by month. All will be default.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 8

If all the data has to be returned all the time, the only way to speed that up is to spend lots and lots of money on hardware. There is no way to tune a query this basically this:
SELECT * FROM TABLE
In that case, you can only buy bigger, faster, disks and lots more memory as well as ensuring that your network speed and bandwidth are as fast as possible.

I'm sure this is the requirement as defined to you, but the reality is, people don't read 60,000 lines of data. They're looking for filters and aggregates. It's that filerting and aggregating that should be done within the query. As a part of become better at this job, you have to learn how to push back on and get modified unrealistic requirements. Just because someone asks for a thing doesn't mean it can be realistically delivered. The key is not to ask them what they want, rather, understand what they need and deliver that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 9

girl_bj - Sunday, December 16, 2018 6:38 PM
Hi,
Every month I will import data into table. The data doesn't have any primary key. It has year and month for user to pull report monthly. It has more than 20+ columns. I am having issue it takes bout 30 seconds or more to extract from the view.
Any way I can solve this issue? Apply indexing will it help? Need some suggestion here.
Thanks

You say "extract from the view"... to where? The screen or what?

You also would do well to add the proper clustered index to help with this and the fact that, someday, you're going to need to do some deletes from this table. In fact, you should start planning on how you want to partition the table so that you can stop doing backups of data that will never change and make it easier to just drop whole months when it finally comes to that... and, trust me... it WILL eventually come to that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)