Yes, it should be slow if you read all 50 million records. There are four full table scans for two NOT EXISTS and two sorts.
And on top of that you have two conditions with Functions in them. You could of course use a computed column index to solve that. But that would depend on which version of SQL Server you are using.
So, what version of SQL Server are you using?
Do you need all data in one go or is it enough to filter out one user at a time?
Im using SQL Server 2008 R2. And also i need to work with limited set of records from the 50 million records. So im planning to take the required records to seperate temp table and planing to execute the query.
Declare@tableAtable ( startdate datetime, Enddate datetime, ID int)
insertinto@tableAselect'2014-01-01', '2014-01-31', 12345 ID unionallselect'2014-02-01', '2014-02-28', 12345unionallselect'2014-03-03', '2014-03-25', 12345unionallselect'2014-04-01', '2014-04-30', 12345unionallselect'2014-05-01', '2014-05-31', 12345unionallselect'2014-07-01', '2014-07-31', 12345select ID, min(cdate) as startdate, max(cdate) as enddate
select startdate as cdate, MONTH(startdate) as mon, ID
from@tableAunionallselect enddate as cdate, Month(enddate) as mon, ID
Groupby ID, mon
Hi all, is there a way to select records from two tables where the right hand side table may or may not have a matching record ? What I'm trying to do is produce a yearly report of sales per month per salesperson and if there are no entries for a given month I still want a record for the salesperson but showing a zero amount. I've looked at various *solutions* on the web but they all produce a null set which is not what I want. Null values for the sales is acceptable but I always want the salesperson name returned
// Table 1 ( Salesperson )
Hi there and thank you for your time, please accept my apologies for the misinformation I was typing from memory on the train, the actual data structures are as follows , as you can see the first table can have many records for owner_id and many records for the same month
owner_id close_date opp_id
Peter 2014-01-02 00:00:00.0001
Peter 2014-01-02 00:00:00.0002
Peter 2014-02-04 00:00:00.0003
Peter 2014-02-04 00:00:00.0004
Peter 2014-03-06 00:00:00.0005
Peter 2014-03-06 00:00:00.0006
Peter 2014-04-08 00:00:00.0007
Paul 2014-01-02 00:00:00.0008
Paul 2014-01-02 00:00:00.0009
Paul 2014-02-02 00:00:00.00010
Paul 2014-02-02 00:00:00.00011
Paul 2014-03-02 00:00:00.00012
Paul 2014-04-02 00:00:00.00013
Paul 2014-04-02 00:00:00.00014
Mary 2014-06-01 00:00:00.00015
is this still doable ?
We can’t stop here, this is bat country - Hunter S Thompson RIP
Schema is the actual structure of the database described in such a form that understandable to humans...
A schema will contain definitions for tables, views, indexes, relations, procedures, functions and every other aspects can be accesses in the db...There is a possibility that for the same database there are several schemas, divided by access rights...So a schema is not equal the database but more a way one can see the database with the proper access rights...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
I'm new to mysql .. I need a One to many ralation tutorial.. like for example I made a table called MovieList with 1 column name TitleMovie and now I want to create another table called acc1_MyWatchlist and inside of it was also a TitleMovie..my question is I want insert a value in acc1_mywatchlist but only the available values in Movielist's TitleMovie and If I tried to create another table acc2_mywaychlist it should be the same as the first table and if I tried to edit some values in the MovieList table it should be affect all the table that has the same column TitleMovie....
in Access database I just use the lookup wizard but ofcourse mysql is differnt so can you give me some examples of this query..
I don't think you understand normalisation and references.
It sounds like your first table should have and ID of some type as well as the title. Other tables would then refer to that ID, not to the title. If you need to alter the title, the IDs stay the same and no other tables need to be updated.