SQLTeam.com | Weblogs | Forums

SQL transaction

sql2012
#1

hi guys. I'm building a database on sql server 2012 for my company.
i have a big trouble with sql server 2012 so i post this request , please help me!
I want to watch 500-data rows in end of the table by query. when data in table is too many ( about some billon rows) , it takes too much time to complete the query.
How do I reslove them
Thank you.

0 Likes

#2

Need alot more information than that. Can you provide more detail as well as maybe an example of exactly what you want? What do you mean by bug trouble with SQL Server 2012? What do you mean by watch 500-data rows??

0 Likes

#3

In addition to what mike01 said, please show the query you're having trouble with. Have you investigated adding an index to speed up the problematic query?

0 Likes

#4

This is a total guess - and will depend on what indexes you have available:

SELECT TOP 500
       *
  FROM yourLargeTable t
 WHERE {whatever filtering you need}
 ORDER BY
       t.keycolumn desc;

The ORDER BY needs to contain the columns that identify end of the table. If you have an identity column on this table - you can use that column.

0 Likes

#5

Thank guys. Let me explain my issue.
My company is using the SCADA software for surveying and controlling the operation of it. The data of operation is link to SQL server 2012. Every day the operator of factory log in SQL server and get daily data to save them on the operation dinary of factory. Them is done through query in SQL server.

One-day data will be at 500 ending-rows of table. But the totalk of rows of one table may be more 1 billion. So the query what show to me daily data takes much time to finish and the error sometimes happen.

In addition, the data raise everyday , the memories aren't capacity enough. So what can I do for resloving them?

Vào Th 5, 4 thg 4, 2019 vào lúc 08:37 Vản Mạnh Khương vanmanh270495@gmail.com đã viết:

0 Likes

#6

How do you identify '500 ending-rows of table'?
How do you identify the 'daily data'?

If you have an IDENTITY column defined - the you can use that to get the TOP 500 based on the IDENTITY column in descending order (the last 500 rows).

If you have a datetime column - you can use that column to filter the data for a specific date.

If you don't have any indexes on those columns, SQL Server must read every row to find the matching rows.

0 Likes