SQL query performance

Question

0.00/5 (No votes)

See more:

My database table has the following columns:
A1, A2, A3, A4, A5

I have to update these records and shift the values left whenever I come across a null column. The purpose is not to have null values between the columns that have a value starting from left. For example if:

A1 = NULL , A2 = 1 , A3 = 4, A4 = 5, A5 = 9

I have to shift the values left so the result will be:

A1 = 1, A2 = 4 , A3 = 5, A4 = 9, A5 = NULL

So far I have come up with the following query but it is slow. Let me know if you can tweak the query to make it faster. One more thing, What if I do this in c#? Will it be faster if I loop through the Datarows there and update each row?

SQL

UPDATE myTable SET
    A5 = A6,
    A6 = NULL
WHERE (A5 IS NULL) AND (NOT A6 IS NULL)

UPDATE myTable SET
    A4 = A5,
    A5 = A6
WHERE (A4 IS NULL) AND (NOT A5 IS NULL)

UPDATE myTable SET
    A3 = A4,
    A4 = A5,
    A5 = A6
WHERE (A3 IS NULL) AND (NOT A4 IS NULL)

UPDATE myTable SET
    A2 = A3,
    A3 = A4,
    A4 = A5,
    A5 = A6
WHERE (A2 IS NULL) AND (NOT A3 IS NULL)

UPDATE myTable SET
    A1 = A2,
    A2 = A3,
    A3 = A4,
    A4 = A5,
    A5 = A6
WHERE (A1 IS NULL) AND (NOT A2 IS NULL)

Posted 22-Jun-11 11:50am

Mastersev

Add a Solution

Comments

AspDotNetDev 22-Jun-11 19:08pm

Have you considered using a cursor?

AspDotNetDev 23-Jun-11 14:56pm

FYI, I don't get notified of your reply unless you click "reply" to my comment rather than clicking "add comment". What do you mean "how can I implement it in this query"? Do you want me to use use only update queries? Or would a cursor be acceptable? Do you need help understanding how a cursor would be implemented?

AspDotNetDev 23-Jun-11 15:29pm

Perhaps. Might be able to get it to perform faster, but the query would be very long. My recommendation would be to go with a cursor unless you have a good reason not to.

AspDotNetDev 23-Jun-11 19:34pm

OK, I added a solution that shows how to do it with a cursor.

2 solutions

Solution 1

SQL

DECLARE @tmpTable as Table(A1 INT, A2 INT, A3 INT, A4 INT, A5 INT)
INSERT INTO @tmpTable(A1,A2,A3,A4,A5) VALUES (NULL,1,4,5,9)

SELECT * FROM @tmpTable

A1          A2          A3          A4          A5
----------- ----------- ----------- ----------- -----------
NULL        1           4           5           9


UPDATE @tmpTable SET A1 = A2, A2=A3, A3 =A4,A4 =A5, A5=A1

SELECT * FROM @tmpTable
A1          A2          A3          A4          A5
----------- ----------- ----------- ----------- -----------
1           4           5           9           NULL

Posted 22-Jun-11 19:48pm

Liju Sankar

Updated 22-Jun-11 19:50pm

v2

Comments

Mastersev 23-Jun-11 10:55am

what if in the second record A3 is null, this wont work.

AspDotNetDev 23-Jun-11 14:57pm

I'm not sure you understand the OP's problem, as this will not work as you have presented it.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

AspDotNetDev · Accepted Answer · 2011-06-23T13:33:00

Try something like this:

SQL

DECLARE @test table (ID int, A1 int, A2 int, A3 int)
INSERT INTO @test VALUES(1, 1, NULL, 3)
INSERT INTO @test VALUES(2, 1, 2, 3)
INSERT INTO @test VALUES(3, NULL,2 , NULL)
DECLARE @temp table (id int, val int)
DECLARE @id int
DECLARE eachRow CURSOR FOR
	SELECT ID, A1, A2, A3 FROM @test WHERE A1 IS NULL OR A2 IS NULL OR A3 IS NULL
DECLARE @rowID int, @A1 int, @A2 int, @A3 int
OPEN eachRow
WHILE 0 = 0
BEGIN
	FETCH NEXT FROM eachRow INTO @rowID, @A1, @A2, @A3
	IF @@FETCH_STATUS != 0 BEGIN BREAK END
	
	DELETE FROM @temp
	SET @id = 1
	IF @A1 IS NOT NULL
	BEGIN
		INSERT INTO @temp VALUES(@id, @A1)
	END
	SELECT @id = ISNULL((SELECT MAX(id) + 1 FROM @temp), 1)	
	IF @A2 IS NOT NULL
	BEGIN
		INSERT INTO @temp VALUES(@id, @A2)
	END
	SELECT @id = ISNULL((SELECT MAX(id) + 1 FROM @temp), 1)
	IF @A3 IS NOT NULL
	BEGIN
		INSERT INTO @temp VALUES(@id, @A3)
	END
	SELECT @id = ISNULL((SELECT MAX(id) + 1 FROM @temp), 1)
	SELECT @A1 = NULL, @A2 = NULL, @A3 = NULL
	SELECT @A1 = val FROM @temp WHERE id = 1
	SELECT @A2 = val FROM @temp WHERE id = 2
	SELECT @A3 = val FROM @temp WHERE id = 3
	UPDATE @test SET
		A1 = @A1, A2 = @A2, A3 = @A3
	WHERE
		ID = @rowID
END
CLOSE eachRow
DEALLOCATE eachRow
SELECT * FROM @test

I created a table variable to test with (I only used A1 through A3, but you can extend it to be through A6). You will want to ensure your table has an ID field with an index on it. Also, you may want to add an index to the temporary table variable I used in my example if the number of fields you have is large. If you actually have 6 rows, then you shouldn't need an index on the temporary table variable.