SQL syntax
-
Lynn,
Thank you for those kind words.
-
Sergiy,
My first line was:
"A very easy question for someone who has the time to respond...."
If you don't have the time, then don't respond. There is no need to be rude.
Thanks to everyone else who responded.
-
Sergiy: "db2l, I believe it's not "SQL syntax" question anymore. It's "I don't want to think" issue."
Sergiy: "How many seconds of thinking effort it takes?"
1st, it was not to you.
2nd, anything is wrong? Any single word?
And yes, I hate people who don't wanna think and look for somebody to do their job for them. Sorry for that.
Sergiy: "And don't tell you are not familiar with index."
Thank you for pointing the typo. Corrected.
Sergiy: "Yes, in this case you do this stupid conversion once."
Conversion stays stupid, regardless who's doing it. I made a ot of stupid conversions and other things before I've learnt better options. Don't see anything wrong in admitting this.
Sergiy: "An did you actually compare the performance of the queries? Do it, and than speak in favour of your option. If you'll find the words."
It was written after your words about hostility. Right, my sensitive little liar?
And ther was a smile at the end to indicate what did it mean.
Sergiy: "Man, get rid of you false pride, your solution is really bad, there is nothing to be proud of, you better appreciate the guy who brought you better option."
Ready to repeat it again.
I've posted a lot of bad solutions here, last time week ago, and people pointed on my mistakes. I thank them a lot. Really appreciate. They helped me to improve quality of many of my solutions.
I must be really stupid to be offenced by pointing on my mistakes. Are you?
_____________
Code for TallyGenerator -
Sergiy,
Tactfully pointing out a better way of doing things is one thing. The way you approached this particular situation was anything but tactful. Using words like "stupid", '"liar", etc. is not conducive to the positive free flow of ideas. Many of us are highly educated and experienced professionals and like to be treated that way. The best way to look at it, is do you want people flaming you for bad or inefficient ideas, or would you prefer that they simply suggest that there is another way to accomplish a task?
Lynn
-
Apart from Books Online, can someone suggest a manual (downloadable) that covers SQL syntax specifically for SQL Server? I don't use SQL Server very often, so I am not really familiar with it. I did a quick search myself, but couldn't find anything. Again, please only respond if you have the time, and know of one off the top of your head. I don't expect anyone to search the Internet for me....
Thanks!
-
I did respond.
And respond was complete.
What you have found missing in that response?
_____________
Code for TallyGenerator -
You requested query for records with date FROM month ago TO moth ago + 1 day. You've got it.
Than you changed request - dates FROM month ago TO ... no limitation or current moment. Right?
So, what you needed to do is just take the query and remove part "TO" or replace it with GETDATE().
Is it too hard?
_____________
Code for TallyGenerator -
Sergiy,
I took your challenge and created a table (actually 2) with 100000 rows. I then used 2 queries one using Edwin's method of determining the starting date and one with yours. I truncated and loaded the table prior to each run of the queries extracting all rows for 1 month, and you know what I found?
sometimes Edwin's ran faster and sometimes yours ran faster. the difference between the two was always less than 100 ms. Not much of difference, and one wasn't always faster than the other.
Here is the code I used:
USE [Sandbox]
GO
/****** Object: Table [dbo].[DateTest1] Script Date: 11/14/2006 23:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DateTest1](
[DateTest1] [datetime] NOT NULL,
[DateTestVal1] [varchar](50) COLLATE Latin1_General_CS_AS NULL,
[DateTestVal2] [varchar](50) COLLATE Latin1_General_CS_AS NULL,
CONSTRAINT [PK_DateTest1] PRIMARY KEY CLUSTERED
(
[DateTest1] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [Sandbox]
GO
/****** Object: Table [dbo].[DateTest2] Script Date: 11/14/2006 23:29:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DateTest2](
[DateTest2] [datetime] NOT NULL,
[DateTestVal1] [varchar](10) COLLATE Latin1_General_CS_AS NULL,
[DateTestVal2] [varchar](10) COLLATE Latin1_General_CS_AS NULL,
CONSTRAINT [PK_DateTest2] PRIMARY KEY CLUSTERED
(
[DateTest2] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
set nocount on
truncate table dbo.DateTest1
truncate table dbo.DateTest2
declare @date datetime,
@cnt int
set @date = dateadd(mm, -2, getdate())
set @cnt = 2
insert into dbo.DateTest1 (DateTest1, DateTestVal1, DateTestVal2)
values (@date, 'AAAAAAAAAA','BBBBBBBBBB')
while @cnt <= 100000
begin
insert into dbo.DateTest1 (DateTest1, DateTestVal1, DateTestVal2)
select dateadd(mi, 1,max(DateTest1)), 'AAAAAAAAAA', 'BBBBBBBBBB'
from dbo.DateTest1
set @cnt = @cnt + 1
end
insert into dbo.DateTest2 (DateTest2, DateTestVal1, DateTestVal2)
select DateTest1, 'AAAAAAAAAA', 'BBBBBBBBBB'
from dbo.DateTest1
declare @timer1s datetime, @timer1e datetime
declare @timer2s datetime, @timer2e datetime
set @timer1s = getdate()
select
*
from
dbo.DateTest1
where
DateTest1 >= DateAdd(Day, 1, DateAdd(Month, -1, Convert(Char(10), GetDate(), 120)))
and DateTest1 < getdate()
set @timer1e = getdate()
set @timer2s = getdate()
select
*
from
dbo.DateTest2
where
DateTest2 >= dateadd(mm, -1, dateadd(dd, datediff(dd, 0, GETDATE()), 0) )
and DateTest2 < getdate() --dateadd(mm, -1, dateadd(dd, datediff(dd, 0, GETDATE()), 0) ) + 1
set @timer2e = getdate()
select @timer1s, @timer1e, datediff(ms, @timer1s, @timer1e), @timer2s, @timer2e, datediff(ms, @timer2s, @timer2e)
set nocount off
-
Sorry to disappoint you, but your test is not right.
1st, Edwin's query was this:
Select * From Table
Where Date(timestamp_column) >= DateAdd(Day, 1, DateAdd(Month, -1, Convert(Char(10), GetDate(), 120)))
I don't see where you included this query into your test.
2nd, your query returns about 1/2 of the table, so gain of using index is not so significant.
In real life sich query will always return from 1% to 5% (if there is archiving for historical data in place) of whole recordset.
Build your test properly and see the difference.
On my machine for 10% of data returned (I replaced "Month" with "wk" in DATEADD) it's 110 ms vs 1150 ms.
_____________
Code for TallyGenerator -
Hi guys,
Just use this query
select * from Table1 where Datecol1 = month(getdate()) AND Datecol1 = day(getdate())
-
Using your own terms:
Challenge met!
Oh, I get it, if the results don't match what is expected, change the requirements of the challenge!
I met the requirements, 100000 records in the table. Pulled all records for the previous 30 days (or so).
At least I was also willing to post the code I used for your challenge, where is your code?
-
An update:
1st, Edwin's query was this:
Select * From Table
Where Date(timestamp_column) >= DateAdd(Day, 1, DateAdd(Month, -1, Convert(Char(10), GetDate(), 120)))
I don't see where you included this query into your test.
You remind me of my kids, they look for things and don't see them when it is right in front of them.
Look again at the first query:
select
*
from
dbo.DateTest1
where
DateTest1 >= DateAdd(Day, 1, DateAdd(Month, -1, Convert(Char(10), GetDate(), 120))) -- RIGHT HERE
and DateTest1 < getdate()
Changing the requirements some, I made a change. I created 2 tables with 16,873,144 records and using the same queries before
with one change to the second query so it matched the first:
select
*
from
dbo.DateTest2
where
DateTest1 >= dateadd(Day, 1,dateadd(mm, -1, dateadd(dd, datediff(dd, 0, GETDATE()), 0) )) -- change is here
and DateTest1 < getdate() --dateadd(mm, -1, dateadd(dd, datediff(dd, 0, GETDATE()), 0) ) + 1
Both queries return 43,759 records. That is 0.25% of the records in each table (good enough to use indexes?).
Again, neither query was consistently faster nor was one always faster than other. Each query completed
within 100 ms or less of each other and it was almost like flipping a coin as to which ran faster.
-
WHERE?
Select * From Table
Where Date(timestamp_column) >= DateAdd(Day, 1, DateAdd(Month, -1, Convert(Char(10), GetDate(), 120)))
Where is it "RIGHT HERE"?
Don't yourself remind you of your kids ?
_____________
Code for TallyGenerator -
Unfortunately, that is DB2 syntax. You have to use T-SQL syntax with MS SQL Server. Even in your own code you used T-SQL.
Original post:
The DB2 equivalent would be:
select * from table
where date(timestamp_column) = current date - 1 month;
No, I don't remind myself of my own kids.
-
> No, I don't remind myself of my own kids.
You should. Because (quote) "they look for things and don't see them when it is right in front of them."
Does syntax matter? Function applied to the column in WHERE clause - that matters. It's wrong no matter which database syntax you are using.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply