C# Discussion Boards - CodeProject

SQL

It appears to be somekind of limitation in the DataGridView control which prohibits use of on the fly created virtual (non-existing) column ...

For now this works for me and maybe as I learn more of DataGridView I will discover more what can and can't be done with it. ... thanks again
                   
                      -- case solved -

I'm working on an app that has custom exceptions defined. When the DAL wants to communicate that something is wrong, not necessarily an error, then it throws a custom exception. The theory here is that the UI can catch them and know what's wrong.

Examples might be a purchase amount exceeding a credit limit. The custom exception AmountExceedsCreditLimitException is thrown, with the message and amount attached to to it.

Then the UI now knows about this and can deal with it.

Now I know some who say that Exceptions should only be used for true errors.

I'd like to get your opinion.

If it's not broken, fix it until it is

I guess there's always people who are going to be polarised one way or another - yet I think the major crime is throwing exceptions, catching them, and chucking them away.

Define 'true error' anyway - surely that's also in the context of the software

At least in your case, the exceptions mean something, and are being caught and handled

Throwing Exception("<blah>") isn't too useful because you can't code for those exceptions (i.e. e is CustomExceptionType). Custom exceptions are better, but you'll find that you'll end up writing THOUSANDS of these AmountExceedsCreditLimitException type classes.

IMHO, your example of AmountExceedsCreditLimitException should not be an exception, but rather a bool return from a method.

Goes back to my first point, once you start doing custom exceptions, its easy to get overzealous and make everything exceptions.

SledgeHammer01 wrote:
Goes back to my first point, once you start doing custom exceptions, its easy to get overzealous and make everything exception

That seems like what's going on here.

The problem is that if you want to return some kind of message to the UI, then the message has to be defined in the DAL or BL, rather than in the UI where it belongs. Using the custom exception tells the UI "Hey, this condition occurred. You decide how to tell the user", rather than "Hey, this condition occurred and here's what to tell the user".

If it's not broken, fix it until it is

Is the user really going to be able to do anything about the error? Most of the time probably not if they are not technical. The .Net framework throws custom exceptions so you might want to be consistent with that. Personally I try to show the user as few errors as possible because they'll just think the software is buggy. Better to try to handle common errors yourself silently and log the ones you can't.

I think this is a great question to throw ( Smile | :)

) out in the community and I am interested in seeing the responses. I am in favor of throwing exceptions on errors and when the code encounters an unexpected situation. Vague, I know. I would not want to make a more detailed statement because it depends on what is considered unexpected in any given function, library or application.

In an application handling charges to credit cards, I do not think it is unexpected that an amount can exceed the available credit limit, so I would be against categorizing that as an exception. It seems to me that if you go down that road, you end up defining the program behavior through the use of exceptions.

Here is a link to what I will normally follow.
http://msdn.microsoft.com/en-us/library/ms173163.aspx[^]

Soren Madsen

"When you don't know what you're doing it's best to do it quickly" - Jase #DuckDynasty

That sounds OK to me, but that still doesn't seem enough like an Exception, and a boolean wouldn't give enough information, a good old enumerated value might be good.

public enum TransactionStatus
{
  Pending
,
  Applied
,
  Rejected
,
  NoSuchAccount
,
  InsufficientFunds
...
}

C#

{ true, false, EFileNoteFound }

?

Needless to say, I'd recommend using the exceptions; yes, there's a performance-penalty, but it's hardly noticeable. Unless it's in a tight loop, you won't notice it.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

The problem with this is that, one, Exceptions are a little expensive to throw, so if you end up throwing a lot of them, well, it adds up.

Two, as the name suggests, is what you're finding "exceptional"? No, it's not. What you're doing is making a determination of an account status as part of a normal business process. You're chosing two different paths to return the two possible states of an account. THe account can either have sufficient credit, or not.

What you should be doing is not relying on exceptions to determine the state of a business object. The business object should be telling you its state. If you have to wrap or include a state object in your transaction object, fine.

Exceptions should be used to transmit information outside of your normal business process, such as if a transaction fails to commit because of a database failure.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Dave Kreskowiak wrote:
the state of a business object

Who said anything about business objects? Cool | :cool:

Dave Kreskowiak wrote:
The problem with this is that, one, Exceptions are a little expensive to throw

This is a bit of a myth.[^] if you run tests with the debugger on you get poor performance, without debugging it is OK.

Otherwise, I agree. We use the exceptions as business logic at work and I don't like it: We've 100s of exception classes (and a deep hierarchy) to handle cases that often should be state within a business object. Not only is there this exception-bloat but it makes stuff harder to trace: I have to work up through the potential call stack to see where something is handled. Not only that we rely on the exception being caught somewhere (if not the user gets an error screen for normal business logic), instead of a more traceable return result (or object state).

I can see where the "exception" camp comes from, but I think they are wrong.

“Education is not the piling on of learning, information, data, facts, skills, or abilities - that's training or instruction - but is rather making visible what is hidden as a seed”
“One of the greatest problems of our time is that many are schooled but few are educated”

Sir Thomas More (1478 – 1535)

Keith Barrow wrote:
This
is a bit of a myth.[^] if you run tests with the debugger on you get poor
performance, without debugging it is OK.

Hang on a second. I did my own testing in Release builds. The problem comes in when the CLR has to search up the call chain to find a handler. It's not so much the Throw that's slow, as it is finding a Catch.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

I'm for avoiding try/catch blocks unless absolutely necessary. It's a heavy operation and should be avoided if you can solve it by using normal means of handling it.

eg. check if the file exists before performing any operation on it (open, write, read) instead of just trying to open/write/read it and catching the exception.

Just my thought.

V.

(MQOTD Rules and previous Solutions )

V. wrote:
It's a heavy operation

Create a console-application, and throw as much exceptions in a loop as possible in a second. How many exceptions did it generate? (Last time I checked, more than 10k on a very slow machine)

It ain't that heavy, unless you have a debugger attached.

V. wrote:
and should be avoided if you can solve it by using normal means of handling it.

That sounds like a religious statement. Do you think an integer representing an error would be easier?

V. wrote:
eg. check if the file exists before performing any operation on it (open, write, read) instead of just trying to open/write/read it and catching the exception.

Checking would be redundant in that case any way; but imagine some object that can have three different problems; would you use integers or exceptions to communicate the "error" to the user?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Eddy Vluggen wrote:
It ain't that heavy, unless you have a debugger attached.

And thus, no problem?

Eddy Vluggen wrote:
That sounds like a religious statement.

Not at all, I use try/catch more than often enough.

Eddy Vluggen wrote:
Do you think an integer representing an error would be easier?

Since your dutch, dat is wel héél kort door de bocht. (sorry didn't know the English expression)

Eddy Vluggen wrote:
Checking would be redundant in that case any way; but imagine some object that can have three different problems; would you use integers or exceptions to communicate the "error" to the user?

You're comparing integers and exceptions which is like comparing apples with pears. I use a descriptive message to show to the user and in that message I'll explain what he can do with it.

Don't shoot me for giving an honest reply. Smile | :)

(I ~~think~~ hope you didn't mean too)

V.

(MQOTD Rules and previous Solutions )

V. wrote:
Don't shoot me for giving an honest reply.

(I ~~think~~ hope you didn't mean too)

I never mean to, but interhuman-communication isn't one of my strong points. Then again, the worst that could happen are a few downvotes, and I might learn something Thumbs Up | :thumbsup:

V. wrote:
And thus, no problem?

Nah; just that it's not a heavy operation. Still, you made me curious. Can you give me an example of the problem you're trying to avoid?

V. wrote:
Since your dutch, dat is wel héél kort door de bocht.

"Kort door de bocht" is geen argument; omrijden is nooit een wijs idee. For the English reader; "kort door de bocht" means taking a (inofficial) shortcut; and in coding, KISS is preferred.

V. wrote:
(sorry didn't know the English expression)

A "jump to conclusions-mat". Seen the movie "Office Space"?

V. wrote:
You're comparing integers and exceptions which is like comparing apples with pears.

No, I'm taking a particular use that we have for exceptions, and wondered what alternatives there could be, and whether they're more preferable or not.

I have seen "True, False, EFileNotFound" in code too often. It's easier to create a new Exception, to throw it, and to handle it. Yes, I'd say that such even goes for the validation of a BO. Why? Because it'll be guaranteed in a "correct" state; no illegal values in any properties, as it would throw an exception.

My old PC did around 10k exceptions in a second (throwing and handling). The one at work did around 33k in a second, and the new PC (new means "from the store a week ago") does 48000 exceptions per second.

..and only 192 in debug mode.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

V. wrote:
It's a heavy operation

Not on modern systems. In the 90s that was true for C++ and probably Java, but I have seen no evidence since that one need be overly concerned about that versus any other obvious performance related issue.

Kevin Marois wrote:
Examples might be a purchase amount exceeding a credit limit.

That specific example means that the database doing business logic so of course it must be capable on communicating a business failure. I suspect that an exception is a convienent and perhaps even the best way to do this.

Naturally if there are a lot of business rules in the database then there will be a lot of exceptions. However I question the need for different types of exceptions. I suspect it could be encoded within a property of the exception and although that seems more complex because there are already so many the complexity is the same.

Hi Guys. Suppose this question has been asked numerous times. I have a database "postgresql" with about 20 columns and about 1.4million records. I select data from the database between two given date ranges and it takes about 4 minutes to fill the dataset. I would like to speed this up. I am doing no updates, just using the data to compile a report. is there a faster way to do this? Below code is what I have. My results are OK but just filling the dataset is a bit of a problem i that it takes +- 4 to 5 minutes to fill the dataset.

C#

            string sql = @"SELECT * FROM datbase where bus_received_time_stamp between @startDate AND @endDate";

            //Making use of a Postgresql Connection helper
            NpgsqlConnection conn = new NpgsqlConnection(conns);

            //Here we format the dateTimePicker dates to be used with the database
            string fromDate = dtStartDate.Text;
            string toDate = dtEndDate.Text;

            //Instanciate a SqlCommand and connect to the DB and fill a Dataset
            NpgsqlCommand cmd = new NpgsqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@startDate", fromDate + " 00:00:01");
            cmd.Parameters.AddWithValue("@endDate", toDate + " 23:59:59");

            #endregion

            conversionRate = txtConversionRate.Text;
            double conRate = Convert.ToDouble(conversionRate);

            try
            {
                //Open the DB Connection
                conn.Open();
                setText(this, "Connection Established");
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                setText(this, "Collecting Data for Processing!");
                da.Fill(dts);
//Time between the two "setText statements takes a good 4 to 5 minutes
                setText(this, "Define Data To be Used");

From here the processing takes a few minutes as there quite a bit of matching to do etc. However the main thing here is that filling the DataAdapter takes too much time. I would like to cut this down to maybe a few seconds, maybe a minute? I have added an Index on the DB on the Datee column to try and speed things up but now sure if this is correct?

Any ideas??

Excellence is doing ordinary things extraordinarily well.

Sounds like you are retrieving a huge dataset and doing calculations in your client code? If these statistics or calculations can be done on the SQL server, it will likely increase performance (because sql servers are really good at aggregating stats) and it will decrease network load, which I suspect is your problem in the first place. Getting 1.4 million rows from the database will always take a long time because it has to transfer all that data over the network. If you only had one boolean column in your table, 1.4 million rows would be 1.3MB of data - your 20 columns could result in your "fill the dataset" being a 20MB download, and that's never gonna be real fast.

To recap - solutions involve reducing the amount of data you need to transfer. Ideally, only transfer exactly what needs to be seen by the user and nothing more. Even if your query is super fast, transferring data over the network isn't. Your index on the date column is about all you can do to speed up the query since it's your only selection criteria, but again, finding the data isn't probably the issue.

Filling the dataset is always going to be the bottleneck in your code.

That being said...

A few "band-aid" fixes:

1) Your query is pretty basic, but you *might* shave off a bit of time by using a stored procedure instead.
2) You *might* shave off a bit of time by indexing the bus_received_time_stamp column
3) Do you have an enterprise grade SQL server? Or are you running it on some re-purposed podunk PC?
4) Are you connecting to the server via gigabit ethernet?

With those band-aid fixes out of the way...:

1) The real cause of your problem is that you are asking for a LOT of data... 1.4M rows x 20 columns = 28M "cells"... if each cell is only 1 byte, thats 28M bytes = 26MB of data you are asking for. Most likely your average cell size could be 10 bytes or even 100 bytes. Now you are asking for 260MB to 2.6GB of data.
2) Its ***highly*** doubtful you need all that data. What are you doing with it? Displaying it to a user? That's great... but whats a person going to do with 1.4M rows of data? Nothing... a human can't process that amount of data.
3) Are you doing some kind of calculation on the data? If so, you can likely do that on the server and then only retrieve the result... or you can set it to run as a job at midnight or something and have it cached...
4) You could also page the data if the user really needs all that data which again I find **highly** doubtful.

You really need to define your problem better.

SledgeHammer01 wrote:
2) Its ***highly*** doubtful you need all that data. What are you doing with it? Displaying it to a user? That's great... but whats a person going to do with 1.4M rows of data? Nothing... a human can't process that amount of data.

Post said it's for a report; but good point Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

If someone plopped a report with 1.4M rows on it on your desk, what are you gonna do with it? I'd toss it in the recycle bin without even looking at it. If he needs all that data to generate a usable report, thats another story. In that case, he should probably do what another poster suggested and run the stats on the server instead of downloading all the data and doing it on the client.

SledgeHammer01 wrote:

If someone plopped a report with 1.4M rows on it on your desk, what are you gonna do with it? I'd toss it in the recycle bin without even looking at it.

I'd do that with any report; then again, sometimes some bureaucrat needs a paper-trail from here to the moon, just to be able to say to his boss that the evidence has been archived.

Yes, I'll come up with the "filter, don't load all"-text, but that's more appropriate for grids/displaying than for reporting. For reporting, I'd state that time matters little.

SledgeHammer01 wrote:
If he needs all that data to generate a usable report, thats another story.

He'll be the one to answer that question, and eventually, you'd still end up writing that report. Keyword here is "needs".

SledgeHammer01 wrote:
In that case, he should probably do what another poster suggested and run the stats on the server instead of downloading all the data and doing it on the client.

It would still need databinding, whether you do it on the client or on the server. I'd suggest doing so on the client; I'd hate to see fifty people abusing the server while they have an idle desktop.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]