|
And as I don't have the machine I am still left with my question. I suppose I can rephrase for clarity: Estimated TPS on a 5 grand machine for the given simple table structure. I am hoping to get at least 7 records per data page to make life simple.
The key structure is actually fairly complicated making the index question too hard to ask for such a simple task so for brevity assume simple, 64 bit integer keys randomly distributed and an equal number of inserts:selects. (my scenario will involve a clustered and a non-clustered index) and millions of records, perhaps more.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
Mike's answer is correct. You really need more info to even estimate the answer. What's the sequential IO rate of the transaction log drive for the database, and is it dedicated to the database or shared between databases? Are the log files on a dedicated drive? As far as reads go, how spread out do you expect the queries to be so that the most queried results will be cached in memory or will the queries be spread out making reads more IO bound?
The last time I speced out a high performance database server, most of the money went into a 20 drive raid pod and memory for read cache processor wasn't much of an issue, but I haven't really looked at any of that stuff since SANs have become more common.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Is it really so hard to either not reply to my question or to give rough estimates?
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
Sorry, I would have found my response useful, the same with Mike's. I have 20 orange trees how many apples can I harvest?
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
My question was more akin to I have 20 acres and a decent tractor how many bales of hay could I expect to harvest. I can pick up the phone right now and get many answers to that question including ranges to include many factors. Yet for some reason there seems to be a desire for people to make posts which don't offer substantiative support. If you don't have an answer or a range and your response is the exact same as someone else's, why bother with it as it certainly doesn't help me out. If I wanted the answer, "It depends on the exact configuration used" I would never have asked the question.
Again, not to point out the obvious, but unless you think I am a complete moron what possible help do you think your post or Mike's offered me?
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
It should tell you that you need to get some more information. For instance you could run some transactions through your dev database note the number of ios generated per transaction and be able get a rough estimate based on the number of ios that your server can handle.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
I really fail to understand how it is so hard to answer a simple question with an answer. Your statement of what I need to do is exactly what I asked from someone else. I hate to be rude, but are you stupid?
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
WTF is wrong with you. You didn't ask a simple question. You asked a complicated question with no clear answer. If you want me to sum it up simply so you might be able to understand: Maybe, it depends on a lot of different factors.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Hi! I have one problem with a query in Sql Server. I want to get all the records requested for some date provided by the user. The type of the record is datetime, and I want to search only by date, like the record is '2008-06-23 10:03:44.627', and I am trying to do something like this '2008-06-23%'
SELECT * FROM Products WHERE Date = '2008-06-23%'
and it gives me error "Conversion failed when converting datetime from character string."
any help how I can get the data?
|
|
|
|
|
your record is like date with time string..So first you have to convert without time string then only you can get....
Rajendran.AL
|
|
|
|
|
This should do it
SELECT * FROM Products WHERE CONVERT(VARCHAR(20),DATE(),105) = '23-06-2008'
Bob
Ashfield Consultants Ltd
|
|
|
|
|
it says:
'DATA' is not a recognized built-in function name.
|
|
|
|
|
That must be because you type DATA instead of DATE
Bob
Ashfield Consultants Ltd
|
|
|
|
|
same error...
'DATE' is not a recognized built-in function name
|
|
|
|
|
Sorry, its my fault. I did the query using getdate() and forgot to take off the brackets when I posted using your code.
SELECT * FROM Products WHERE CONVERT(VARCHAR(20),DATE,105) = '23-06-2008'
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thx...that was the answer, have a nice day.
|
|
|
|
|
Thats OK, sorry I messed up on my first reply. 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
if you have time, can you please describe what does it mean all that stuff in the CONVERT block. Thx
|
|
|
|
|
CONVERT(VARCHAR(20),DATE(),105)
VARCHAR is the datatype to convetr to. It should really have a length - say varchar(20) but defined as I do it just returns the correct number of characters.
DATE was your database column. For testing I tend to use getdate()
105 is the date format. There are loads of them, 101 to about 114, which return the date and/or time in different formats. Experiment to see which you like best.
Use the BOL to see the different formats.
Hope this helps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Bob, use of most functions on the fields rather than on the parameter will inhibit the use of indexes. This could cause a fast index seek to turn into an index scan.
For this query I would use WHERE DATE >= '20080623' AND DATE < '20080624'. When you construct a date in this fashion it actually produces a datetime where the time parts are set to zero.
Also be aware that all date formats are open to interpretation based on server locale and user language, except ISO 8601 format YYYYMMDD.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Mike
I know that using functions stop indexes working, and that date formats are subject to locale etc, but thanks for pointing it out as others may not be aware - certainly judging by some of the questions on these forums 
My usual solution is to use a stored proc, pass in the date as a param, have a @fromdate and a @todate and do the >= and < as you say. As an aside, if you have used partitioned tables, partitioned on dates, have you noticed if you use a literal (eg addeddate > '23 jun 2008') it just looks at the relevant partition, but using a variable it has a quick squint in all the partitions? You would have thought it would be clever enough to avoid this overhead.
Bob
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Yeah, what Mike said, plus, the above code results in
B) string compares rather than numeric compares
C) performing a conversion on many (millions of?) values rather than one (or two)
For these reasons, never use this technique; it's a coding horror.
[Anecdote:]
Not too long ago (has it been five years already?) I had to add some functionality to a program that already took forty minutes to run.
After adding the functionality and changing the above to the form Mike describes, it took only ten minutes.
The previous guy had said, "well it ain't broke..."
|
|
|
|
|
Hi
I'm trying to use the Iif function. I've had a look on MSDN and it seems like it is valid in SQL server express but I get an error saying that there is an error near the = sign in the Iif function.
I'm using IIF(IsConsultant = -1, 'Yes', '') AS Consultant which is giving me the error.
Am I using the wrong function for SSE or am I doing something wrong. The data type for the IsConsultant field is int
Many thanks
The FoZ
|
|
|
|
|
In SQL Server, you have the CASE statement to do this:
SELECT CASE IsConsultant WHEN -1 THEN 'Yes' ELSE '' END AS Consultant
|
|
|
|
|
Thanks for that. Its something the book I've got didn't touch upon.
The FoZ
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
