Database

Re: SQL Server Time data type and negative TimeSpan values?

9-Sep-11 7:16

kbalias wrote:
I get the TimeSpan value by subtracting the official working hours and the actual working hours.

Which would seem to me that you would need to store the "official working hours" as well. Given that then there is no point in storing the difference for the second value. Instead you just store the "actual working hours". If you need the difference then the caller subtracts the two.

If you want the difference anyways then store it as seconds. That insures enough precision and is probably a more business friendly value than Ticks.

Jörgen Andersson9-Sep-11 8:18

Jörgen Andersson

9-Sep-11 8:18

<Getting my brain back into gear>
The standard way is to store the login and logout times in a table. The nettime is easily enough calculated from those two values. It's a part of normalization to not store values you can calculate from other columns in the database.

List of common misconceptions

Re: SQL Server Time data type and negative TimeSpan values?

Wendelius9-Sep-11 11:18

Re: SQL Server Time data type and negative TimeSpan values?

9-Sep-11 11:18

Agree with this. Answer upvoted.

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

Wendelius9-Sep-11 11:17

9-Sep-11 11:17

I agree with Jörgen. It isn't advisable to store calculated values in the database. If any of the values used in a calculation changes you have to do a recalculation to keep the data up-to-date.

Consider the following:

SQL

create table timetest (
   starttime datetime,
   endtime   datetime
);

insert into timetest values ( getdate(), getdate() - 1);
insert into timetest values ( getdate(), getdate() + 1);

select starttime, endtime, DATEDIFF(n, starttime, endtime)  from timetest;

if either one of the datetime columns change, the result is still always correct.

However, what you can do is that you can specify a computed column if you like. To extend the previous example:

SQL

alter table timetest add timediff  as DATEDIFF(n, starttime, endtime)

Now if you run a select * query to the table you will see the same results as in the previous example because the calculation is now part of the table structure (and always up-to-date).

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

SQL Paging

benams7-Sep-11 0:14

benams

7-Sep-11 0:14

hi,
I'm using an asp.net application with some sql SELECT statements, I want to ask you when is it worthwhile to execute statements with SQL query?
I guess that if I use the paging to get 10-rows pages from a 25-rows table, it's not profitable, and I have to do the paging at the server side code. But when my table has 10000 rows, it becomes worthwhile enough.

Am I right?

Wendelius7-Sep-11 0:36

7-Sep-11 0:36

In my opinion you're right on track.

However, things could get complicated if the amount of rows in the result set is changing (because the amount of rows changes, criteria changes etc). So a straightforward solution would be using paging nevertheless. If the connection between the application and the database isn't slow and the database isn't over utilized, this shouldn't cause too much wait time.

Of course if you have tables that you know are always small, fetching all one time and then paging locally would be an easy thing to do.

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

jschell7-Sep-11 8:49

7-Sep-11 8:49

At some point you are presenting too much data to a user.

If you are looking at a 25 row limit per page then probably the limit is well below 500 total rows.

At that point you know that the user knows what they are looking for. They are not just randomly scanning records. So make them tell you what they are looking for. Use that to create a query that restricts the total rows returned.

When creating servers I usually have a configurable maximum and user queries are run with a count(...) first and if the return is more than a maximum then I return an error. The GUI screens are responsible for providing sufficient input specific to one query to allow the user to provide enough data to get below the limit.

Mycroft Holmes7-Sep-11 12:50

Mycroft Holmes

7-Sep-11 12:50

I you are returning 10k rows to a web browser you are sacked on the spot! One of the issues we have is that the users are always after data dumps to analyse in excel, the continious whine of just give me a data dump becomes really irritating. Now that we have volume policies it is easier to limit these.

As others have said, you need to filter the results to minimise the volume returned. I never use paging although I do use a top 1000 in procs with a potential of high volume and then inform the user they have exceeded the volume policy. By the time you chuck 1000 records into a grid with local filtering and sorting I see no requirement for paging.

Never underestimate the power of human stupidity
RAH

SQL server and multi core cpu utilization

David Skelly7-Sep-11 22:24

David Skelly

7-Sep-11 22:24

Mycroft Holmes wrote:
One of the issues we have is that the users are always after data dumps to analyse in excel

Does that not imply that something is missing from the application? I'm guessing because I don't know the business requirements, but if they are constantly extracting data into another tool to analyse, that suggests that they need some sort of information they can't get from the source application.

Mehdi Gholam6-Sep-11 18:12

Mehdi Gholam

6-Sep-11 18:12

I have encountered this with several server, the cpu utilization with SQL server on multi core systems is very low i.e. one core does all the work and the others are idle, what is worse is that the queries are queued serially.

Anyone encountered this, is there a solution.

Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.

Wendelius6-Sep-11 19:50

6-Sep-11 19:50

Have you checked if processor affinity is defined? It could be that only one core is allowed. Also it could be that the disk subsystem is actually the bottleneck so CPU has to wait for the answers.

And what comes to the serial execution, I thing there are at least few possibilities:
- again waiting for disk subsystem
- serial executions is forced because of locking issues
- transactions are ran in serializable isolation level.

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

Mehdi Gholam6-Sep-11 21:23

Mehdi Gholam

6-Sep-11 21:23

Thanks Mika, I will look into it...

In the meantime more information:

- We have a windows application server service connecting to sql server via integrated security on an internal threadpool of 10 threads. Each thread creates a connection when needed, and closes when finished.

- The server is an HP with 2 quad core xeon hyperthreaded (16 processors in windows) with 10k sata raid 5.

- Clients connect to the application server via tcp (no direct connection to sql).

Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.

Wendelius6-Sep-11 21:57

6-Sep-11 21:57

Ok, that's good background info. If you find anything suspicious or more information regarding the problem, let us know Smile | :)

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

jschell7-Sep-11 8:50

7-Sep-11 8:50

How do you know that the application server is not the one that is queuing them serially?

Mehdi Gholam7-Sep-11 9:12

Mehdi Gholam

7-Sep-11 9:12

Fair question, I presume when you have parallel threads each with their own connection they query in parallel.

Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.

jschell8-Sep-11 8:33

Picture's in the database?

8-Sep-11 8:33

Seems reasonable as long as you have verified that. And also verified that it is not in some other way serializing requests.

David C# Hobbyist.6-Sep-11 11:55

6-Sep-11 11:55

I just spent 2 hours googleing best practices for storing picture's for a asp.net MVC project that I am starting.

There are two school's of thought. One is to store the Picture's in a database (for ease of backup's) Another is to use the site's file system. (For speed)

This project will be for my family.(Very Large Family I might add) I want to keep Pictures Referenced to the user that uploaded them. And only share some of them with other member's

My question: What do You think about storing the file's in the database? And why?

Thanks in advance

Any good resources or link's appreciated

Frazzle the name say's it all

Mycroft Holmes6-Sep-11 12:39

Mycroft Holmes

6-Sep-11 12:39

I use the file system but then I have 1000s of images, the economy of backing up the database and moving it to my dev environment alone dictates that I do not want the image files inside that backup. What do I care if the images are trashed on the server, I have less timely backups of them elsewhere, I certainly don't want to move them over the wire every time I take a backup of my data.

Never underestimate the power of human stupidity
RAH

David C# Hobbyist.6-Sep-11 15:04

6-Sep-11 15:04

Mycroft Holmes wrote:
I certainly don't want to move them over the wire every time I take a backup of my data.

That is a good point I don't think I will have that many images but you never know at design time If the project will be a hit with the User's.

Frazzle the name say's it all

PIEBALDconsult6-Sep-11 14:48

PIEBALDconsult

6-Sep-11 14:48

I put them in the database, they're safer there. In the file system, someone may overwrite* or delete a file. I don't backup my databases though. Unsure | :~

* Replace your favorite shot of Granny with a picture from your cousin's bachelor party (or vice versa).

David C# Hobbyist.6-Sep-11 15:00

6-Sep-11 15:00

PIEBALDconsult wrote:
* Replace your favorite shot of Granny with a picture from your cousin's bachelor party (or vice versa).

as I am almost a grandfather This would make me Upset!!!

Thank You for the reply I will consider this issue when I Finally Smile | :)

decide which way to go

Frazzle the name say's it all

Pradeep Shukla6-Sep-11 18:20

Pradeep Shukla

6-Sep-11 18:20

There are few questions that you should have answer to make this decision:

File System:

- Images sizes are smaller than 2 MB.
- No editing required on the images in future
- Faster access

Database:

- Image size is on a higher side 2+ MB.
- Easy maintenance and backup required

There is an another approach of FILESTREAM if you are planning to use sql server 2008.
http://msdn.microsoft.com/en-us/library/cc716724.aspx[^]

hope it helps.

Wendelius6-Sep-11 19:45

6-Sep-11 19:45

Here's an short article I wrote some tome ago concerning binary data in the database. Perhaps it would be of some help: How to store and fetch binary data into a file stream column[^]

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

David C# Hobbyist.7-Sep-11 7:41

7-Sep-11 7:41

I just read your article. Interesting so basically all that gets stored in the database is a GUID and correct me if I'm wrong a Hash of the file size? And the file itself goes in the file system? I don't see the benefit in storing the info 2 times. Other than maybe so it can be strongly typed.

Maybe I missed the Point I will reread the article

Frazzle the name say's it all

Wendelius7-Sep-11 7:52