Database

9-Jun-10 3:13

Hi everyone,
I'm using a huge database of geostatistical blocks (million of records). I would like to read the records one by one and process only some. I tried the datareader but it's slowing terribly. Is there a read next or something like that in SQL?

I'm programming in C#.

Thanks in advance,
Yvon

R. Giskard Reventlov9-Jun-10 3:31

R. Giskard Reventlov

9-Jun-10 3:31

If you are only processing a subset of records why not cut down the size of the result set by using a where clause to filter out those records you will not need? (Sorry if you already know that but your question isn't clear).

Like:

select * from [table] where [column] = 'foo'

It's best to bring back only those records you'll actually need: perhaps if you were to explain your need a little more clearly: e.g. are you intending to do this in Sql or c#?

me, me, me

"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven

nils illegitimus carborundum

Yvon Bourgouin9-Jun-10 3:53

9-Jun-10 3:53

In fact, I had something like SELECT [IJK],[XC],[YC],[ZC] FROM [MSDP]; and it was taking 10 to 15 minutes to get the records.
I tried to change it to SELECT TOP 200 [IJK],[XC],[YC],[ZC] FROM [MSDP] ORDER BY [IJK]. It's fast for the first 200 records.

To get the following records, I use SELECT TOP 200 [IJK],[XC],[YC],[ZC] FROM [MSDP] WHERE [IJK] NOT IN (SELECT TOP 200 [IJK],[XC],[YC],[ZC] FROM [MSDP] ORDER BY [IJK]) ORDER BY [IJK];

the second 200 changes in a loop to 400,600 and so on.

At the beginning, it's quite fast but the performances are degrading quite fast.

Any help?

R. Giskard Reventlov9-Jun-10 4:45

R. Giskard Reventlov

9-Jun-10 4:45

Does it need to be ordered? If it does at least put an index on [IJK] that should help. Consider indexing the other columns. Have you created an execution plan? How does that look?

Scubapro9-Jun-10 4:50

Scubapro

9-Jun-10 4:50

This way your selecting all records (however in parts though).
Where's your filter (WHERE CLAUSE), since you need only some records?

Yvon Bourgouin9-Jun-10 4:58

9-Jun-10 4:58

I didn't put the WHERE clause because it's very complex but it's working.

Johan Hakkesteegt10-Jun-10 3:09

Johan Hakkesteegt

10-Jun-10 3:09

Your logic must be really really really complex, if it can not be translated to a WHERE clause.

However assuming that you are right, you can always at least cut out some part of the records with the WHERE clause. Look at the search logic you have written in your application, find the single largest search parameter, and translate only that to sql.

My advice is free, and you may get what you paid for.

supercat99-Jun-10 5:43

supercat9

9-Jun-10 5:43

That is going to be really slow, since retrieving e.g. records 4801-5000 will use 5,000 records' worth of processing to retrieve 200 records; retrieving records 49,801-50,000 will use 50,000 records' worth of processing to retrieve 200 records. To get through 1,000,000 records would require processing about 2,500,000,000.

To add insult to injury, deleting records from the table may cause some records (not involved in the deletion) to be skipped. Adding records may cause some records to be duplicated, but that's a far smaller problem.

If the value in column IJK uniquely identifies records, use "SELECT TOP 200 ... WHERE [IJK] > @lastijk" where parameter @lastijk is the last value you retrieved. I'd guess that IJK is probably unique, since if it isn't your code won't work properly at any speed, but if it is not unique, use "SELECT TOP 200 ... WHERE [IJK] > @lastijk" followed by "SELECT ... WHERE [IJK] = @lastijk" (no TOP 200 qualifier there, and use the last record from the first query for @lastijk); all of the records where IJK equals @lastijk will appear in the second query, so you should ignore any items from the first query where IJK equals @lastijk.

Incidentally, to get reasonable performance, you'll need to have an index--preferably clustered--on @lastijk. I'd guess you probably do have one, or else your performance would be really slow for even the first 200 records.

Yvon Bourgouin9-Jun-10 8:55

9-Jun-10 8:55

Thanks, looks good to me, the performance was degrading quickly, this would probably fix it...

David Skelly9-Jun-10 5:44

David Skelly

9-Jun-10 5:44

Yvon Bourgouin wrote:
At the beginning, it's quite fast but the performances are degrading quite fast

I'm not surprised. Think about what you are asking the database to do here:

SELECT TOP 200...

SELECT TOP 200 WHERE NOT IN (SELECT TOP 200...)

SELECT TOP 200 WHERE NOT IN (SELECT TOP 400...)

SELECT TOP 200 WHERE NOT IN (SELECT TOP 600...)

... repeat lots of times ...

SELECT TOP 200 WHERE NOT IN (SELECT TOP 1,000,000...)

If I understand you correctly, you are running the same query over and over, asking the database to do more work every time.

You say that you are only processing a small number of the rows. So, why fetch them all? Why not put something into the WHERE clause to cut down the number of rows you select? Once you have fetched these rows, how do you decide which ones you will process and which ones you will ignore and what is stopping you from moving that selection logic back to the database? Sorry if that's obvious, I'm sure you have considered it, but I don't quite understand why you don't do that.

i.j.russell9-Jun-10 8:20

i.j.russell

9-Jun-10 8:20

Returning millions of records takes time. If you can't limit the results via a WHERE clause, you might like to try the following approach;

CREATE PROCEDURE GetData
(
	@start INT
)
AS
WITH Query
AS
(
	SELECT 
		ROW_NUMBER() OVER
		(
			ORDER BY [IJK] ASC
		) as position,
		[IJK],
		[XC],
		[YC],
		[ZC]
	FROM [MSDP]
)
SELECT [IJK],
	[XC],
	[YC],
	[ZC]
FROM Query
WHERE position BETWEEN @start AND @start + 200

To run the sp, you use

EXEC GetData 200000

[Replace the 200000 with the start value]. You could also try returning more than 200 records by changing the sp.

Yvon Bourgouin9-Jun-10 8:53

9-Jun-10 8:53

Thanks, that might solve my problem...

MS SQL permissions mangement

J4amieC9-Jun-10 4:54

J4amieC

9-Jun-10 4:54

Yvon Bourgouin wrote:
million of records

Yvon Bourgouin wrote:
I would like to read the records one by one

Can you spot the problem here. Trying to iterate over millions of rows will, of course, be slow.

Why would you not limit the returned results to the rows you actually want?

T M Gray8-Jun-10 10:21

T M Gray

8-Jun-10 10:21

I just had to go through and add view definition and execute permission to a couple dozen scalar value functions for a user. I had to right click on each one, click permissions, click search, type the login, click check names, then check the two permissions and click OK. I could have sworn there was a way to manage object permissions in bulk for a given user but I can't seem to find it.

Re: MS SQL permissions mangement

Mycroft Holmes8-Jun-10 15:07

Re: MS SQL permissions mangement

8-Jun-10 15:07

It really depends on your set up, you could have set up and AD group and manged the users via AD and simply added the group (when searching) to the functions.

You could have used a script to do the same job. We have a set up where the admin can assign procs to users via the apps UI and script the change.

If you restore the database to another server you may run into the situation where the perissions are named the same but have a different ID and are no longer valid - you need to repeat the process.

Never underestimate the power of human stupidity
RAH

T M Gray9-Jun-10 8:06

T M Gray

9-Jun-10 8:06

The problem isn't the number of users. The problem is the number of database objects. I want to bring up a list of scalar valued functions and apply permissions for a single user or group to multiple functions at once. Even if I scripted it that would mean a separate grant statement for each function.

Re: MS SQL permissions mangement

Mycroft Holmes9-Jun-10 12:52

9-Jun-10 12:52

You could employ schema to segregate your objects (I don't and think it would be irritating to have to include schema in every query), otherwise you have to live with your decision to apply user level permissions to database objects.

I'm curious, do your users access your database direct (SSMS/QA) or only through an application?

Never underestimate the power of human stupidity
RAH

Database Size

It_tech8-Jun-10 5:38

It_tech

8-Jun-10 5:38

Hi All,

Recently ,when i try to restore files from my back up (MyDB.BAK) i found two entries one Saying a Fullbackup and another backup which contains the date of backup
Why i am getting two different entries i really dont know.Previously,I only used to get a single file for the current date backup.

Have i changed something to get a duplicate backup?

As the size of the file is now Doulbled from the one i had two months ago?

I am just doing a full backup.

In short this Backup (myDB.bak) is contains two files of same type.Its a duplicate. As a result its giving me double the size of my DB.

Is there any way i could get only one backup?

Thanks

Re: Database Size

Mycroft Holmes8-Jun-10 15:02

8-Jun-10 15:02

I suspect you checked append when selecting the destination (in SSMS). When restoring, via SSMS, you can select the backup you want to restore. I am surprised that restoring both set doubles the size of your database though, I would expect it to overwrite.

Never underestimate the power of human stupidity
RAH

Re: Database Size

It_tech8-Jun-10 21:26

It_tech

8-Jun-10 21:26

Thanks so much for your response.

I did run the database tuning advicer and sql profiler a couple of weekes ago.Can this increase my transaction log files termendeously.At the moment the log files is about 1.1GB,Is there any tool i can use to get the history of this log file in the last few months?

Other wise ,How can i shrink the logfile? Is there any other tool i can use which can tell me why database has grown by double size in only few weeks.

Many thanks.

Re: Database Size

Mycroft Holmes8-Jun-10 22:35