Database

J4amieC19-Jan-09 22:17

J4amieC

19-Jan-09 22:17

Keep it in the same discussion geoorge.

You have already been given a solution to this by Rob: http://www.codeproject.com/script/Forums/View.aspx?fid=1725&msg=2886661[^]

George_George20-Jan-09 2:51

20-Jan-09 2:51

Thanks J4amieC,

My question is different. Smile | :)

Whether RAISEERROR must be used in catch block? Any ideas or comments?

regards,
George

Hamid_RT22-Jan-09 23:18

Hamid_RT

22-Jan-09 23:18

Using RAISERROR[^].

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

George_George4-Feb-09 22:44

Optimize an query select DB

4-Feb-09 22:44

Thanks Hamid!

I like this link you recommended!

regards,
George

TuanNGUYEN19-Jan-09 16:48

TuanNGUYEN

19-Jan-09 16:48

Hi all,
I have a table whick have got 2 fields [id - info], for example :
id info
1 A
2 B
3 C
...

Now, in my program, there is an array of id in program, ex : [1, 3, ...]. Now, I want to query the table to get the corresponding array of objects[id-info] like that : [[1, A], [3, C]...]. I knew some ways, but I think theys are still optimized, as following, 2 ways I knew :
- foreach array of id, get info correspoding the id, then add to the result array => each id, I have to query the DB 1 time => not good.
- generate a big select query with WHERE Condition is like (where id = 1 or id = 2 ...) => query DB 1 time, but the condition expression is complex to verify for DB.
- generate a big select query with Where condition is like (where id in[1, 2, 3]) => similair to 2rd way, not very good.
I think there are still better way, could you help me.
Currently, I am using C# with MSSQL, but I will change to MySQL in several months.
Thanks in advance.

Re: Optimize an query select DB

Wendelius19-Jan-09 18:19

Re: Optimize an query select DB

19-Jan-09 18:19

You could modify the in list to a XML query and use that, but since you're changing ty MySQL that most likely won't work in there. Using IN (value1, value2, ...) is supported by all DBMS although each DBMS may have restrictions in the amount of values. However, that's a working solution and the optimizer tries to find the best permutation for execution.

If the in list is large, this could be a modeling issue, but without anymore info about the problem, it's hard to say.

The need to optimize rises from a bad design.My articles[^]

TuanNGUYEN19-Jan-09 20:04

TuanNGUYEN

19-Jan-09 20:04

Yeah, I am agree, using IN expression is the best way in those three ways. But, in algorithm view, if we can convert the array of Id to a temporary table which have only field "Id", then we inner join two table on "Id". I think if the conversion is possible, that is the best solution, I have not much experience on SQL and programming, I still hope find a better way.

Re: Optimize an query select DB

Wendelius20-Jan-09 8:28

"begin catch" and "end catch"

20-Jan-09 8:28

That's true. If you would have the data in db (in some table format) that would be the best for the query itself. However, how would you get the data to the temporary table. In worst scenario you add it row by row. Little better if you can add them simultaneously, but still you're creating a table and populating it and both of those are time and resource consuming operations. So in overall even if the last query gets simplified, the overall throughput may be worse.

This is exactly why I mentioned the modeling of the db. If you could prevent those dynamic lists or make them significantly smaller, it would have a great impact to your query. But in order to do that, you would have to find common "feature" from the values, which you should then store in lookup tables. I can't say if this is possible at all or how it would be implemented in your case since it would require a whole lot of information about the issue.

One possibility you could use is to create a procedure which gets an array (or user defined table) of values and then uses a those values in join using the original query. I wrote a brief article on that: http://www.codeproject.com/KB/database/TableValuedParameters.aspx[^] but the downside is that it's SQL Server specific.

The need to optimize rises from a bad design.My articles[^]

George_George19-Jan-09 2:38

19-Jan-09 2:38

Hello everyone,

I am not sure my following code must be put between "begin catch" and "end catch"? Or they could be in any where in a store procedure -- e.g. outside "begin catch" and "end catch"?

IF @@ERROR <> 0
BEGIN
 IF @@TRANCOUNT > 0
  ROLLBACK
 DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
 SELECT @ErrMsg = ERROR_MESSAGE(),
      @ErrSeverity = ERROR_SEVERITY()
 RAISERROR(@ErrMsg, @ErrSeverity, 1)
END -- end of IF @@ERROR <> 0

thanks in advance,
George

J4amieC19-Jan-09 4:02

J4amieC

19-Jan-09 4:02

1.
Read the documentation on SQL Books Online about BEGIN CATCH ... END CATCH

edit: I know you're too bone-idle to search the internets, so here you go: http://technet.microsoft.com/en-us/library/ms175976.aspx[^]

2.

Try it out for yourself, observe the behaviour, feel free to ask a question about a specific aspect of the behaviour observed if you need to.

George_George19-Jan-09 18:41

19-Jan-09 18:41

Thanks J4amieC,

The link looks great! But does not answer all of my questions.

My scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.

In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?

regards,
George

Rob Philpott19-Jan-09 6:29

Rob Philpott

19-Jan-09 6:29

Begin/End catch was added in Sql Server 2005 and is kind of handy to avoid having to check the status of @error at multiple points in your stored proc.

The code you've pasted looks like it would belong in a catch. A common pattern looks something like this:

begin transaction
begin try
  ...
  ... 
  ...
  commit transaction
end try
begin catch
  rollback transaction
  raiserror(ERROR_MESSAGE(), ERROR_SEVERITY())
end catch

You don't need to check @error any more - that is implied by reaching the catch block. Can't see any point in checking @@trancount either - either the commit or the rollback will get hit.

Regards,
Rob Philpott.

George_George19-Jan-09 18:40

19-Jan-09 18:40

Thanks Rob!

Your code sample looks great! But it covers how to handle and raise exception to client (e.g. an ADO.Net client program in my scenario) when there is transaction and try/catch.

But my scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.

In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?

regards,
George

Rob Philpott19-Jan-09 23:13

Rob Philpott

19-Jan-09 23:13

Hey George,

Well, if I understand it right and that you have a simple stored proc which is just a single insert/update/delete etc. you won't be needing a transaction as the command will either succeed or fail, so no rollback will be needed.

In this case, from code, you can just call the stored proc directly. Should it fail (FK constraint for instance) this will be raised as an SQLException in the code. This comes for free, you don't need to do anything to get this.

If you've got a batch of commands which you want to work atomically (either all complete successfully or none do (rollback)) you'll want to wrap the sql in a transaction in the proc (as originally posted).

There is the ability to create transactions on the Connection object in code, but personally I wouldn't do this. Transactions should always be as small as possible, and you can't really control the scope as effectively.

Hope that makes some sort of sense.

Regards,
Rob Philpott.

George_George20-Jan-09 3:01

20-Jan-09 3:01

Thanks Rob,

I have verified your solution works. A further question, must we use raiseerror inside catch block? Could it be used outside catch block?

regards,
George

Rob Philpott20-Jan-09 3:13

Rob Philpott

20-Jan-09 3:13

I don't think so - not 100% to be honest. Why would you do this though? Surely you would only want to raise an error in the event that an exception has been raised and caught in the catch block?

Raising it elsewhere doesn't make much sense to me.

Regards,
Rob Philpott.

Wendelius19-Jan-09 7:06

19-Jan-09 7:06

George_George wrote:
am not sure my following code must be put between "begin catch" and "end catch"?

Why not? It simplifies error handling and provides an easy way to do some stuff in an error and exit cleanly.

I suppose that the @trancount condition is based on our previous conversation about transaction scope. If that's true the implementation isn't quite what I suggested (the original suggestion was, if transaction is started outside the scope, don't rollback).

The need to optimize rises from a bad design.My articles[^]

George_George19-Jan-09 18:51

19-Jan-09 18:51

Hi Mika!

1.

"If that's true the implementation isn't quite what I suggested (the original suggestion was, if transaction is started outside the scope, don't rollback)." -- in my situation, all transaction are started at server. I think you mean if transaction are started at client, it is client's responsibility to rollback transaction?

2.

I think it is always better to implement transaction at server side, not client side, correct?

(I think the benefit is, keep transparent to client side.)

3.

I know using unified try/catch error handling is good. But my scenario is different. Some store procedure I am maintaining is simple, just a wrap of a single insert/delete/update statement, and they are not in a transaction -- they also do not wrapped in try/catch.

In this situation, what is your suggested solution to return meaningful error information to client (e.g. an ADO.Net client program in my scenario)?

regards,
George

Wendelius20-Jan-09 6:39

20-Jan-09 6:39

George_George wrote:
I think you mean if transaction are started at client, it is client's responsibility to rollback transaction

Not exactly. What if the transaaction was started by another procedure which then calls this one.

George_George wrote:
I think it is always better to implement transaction at server side, not client side, correct?

This would be a matter of opinion and architecture decision.

George_George wrote:
In this situation, what is your suggested solution to return meaningful error information to client

Depending on the situtation, I would return the original error or use RAISERROR to add more descriptive information.

The need to optimize rises from a bad design.My articles[^]

Hamid_RT22-Jan-09 23:18

Hamid_RT

22-Jan-09 23:18

See Using TRY...CATCH in Transact-SQL[^].

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

Convert Columns as Rows

Vignesh Krishnan19-Jan-09 1:40

Vignesh Krishnan

19-Jan-09 1:40

Hi Guys,

Does any body tel me How to COnvert Column in to rows
Say Examples my Table contains
A B C D
1 1 3 5
2 5 6 7

I want this to be shown as

columnNames Value1 value2
A 1 2
B 1 5
C 3 6
D 5 7

any body assist me on this??

Wendelius19-Jan-09 2:05

19-Jan-09 2:05

You could have a look at pivoting[^].

The need to optimize rises from a bad design.My articles[^]

Vignesh Krishnan19-Jan-09 3:12

Vignesh Krishnan

19-Jan-09 3:12

Thanks Buddy and could YOU tel me the best URL or PDF to study abt SQL SERVER REPORTING SERVICES

Wendelius19-Jan-09 6:55

19-Jan-09 6:55

Vignesh Krishnan wrote:
Thanks Buddy

You're welcome

Vignesh Krishnan wrote:
the best URL or PDF to study abt SQL SERVER REPORTING SERVICES

Don't know what's best, but you could start from here: Tutorials (Reporting Services)[^]

The need to optimize rises from a bad design.My articles[^]