|
That is very very bad. Highly inefficient. You convert every (millions?) SaveDate value and then do a string compare -- which is much less efficient than the DATETIME compare would be.
<anecdote>
I once fixed a program that was written that way -- before I fixed it it took forty minutes to run, afterward it took only ten minutes.
</anecdote>
DON'T EVER DO THAT!! 
|
|
|
|
|
Hello,
I am very green to sql, and normally program in c sharp. I have 2 GUI generated sql queries that I'd like to combine, then exclude the results (users) that are found in the second query, from the first query and finally return the new result set that has the second query data removed from the first data query.
Here are the queries:
Thanks for reading.
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE _QA.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _QA.ProcessID = 0) AS _SA
WHERE _SA.UserSID = AET.UserSID)
ORDER BY TimeDetected DESC,
UserName DESC
SELECT AED.ObjectName,
AED.ObjectCanonical,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AEM.EventMessage,
AET.TimeDetected,
ECS.SubsystemID,
AET.UserName,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = 'C37D85D6-B725-4E98-A338-B8917CA5784F'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
ORDER BY TimeDetected DESC,
UserName DESC
|
|
|
|
|
Read up on Union or Union All
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can use "Except Distinct" !
|
|
|
|
|
INTERSECT might also be useful for this scenario.
|
|
|
|
|
Hello,
I have tried inserting those commands into various locations in the query, without success. Could someone give me an example please?
|
|
|
|
|
use EXCEPT between two queries
|
|
|
|
|
It uses And UserName Not In, and brings in the second search to compare.
-- Mailbox size query users who are set to unlimited, distinct username field with disabled mailboxes removed from search
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-08T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE _QA.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _QA.ProcessID = 0) AS _SA
WHERE _SA.UserSID = AET.UserSID)
AND ValueNew = '<Not-Set>'
AND UserName NOT IN (
SELECT DISTINCT --AED.ObjectName,
--AED.ObjectCanonical,
-- AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
-- AEM.EventMessage,
-- AET.TimeDetected,
-- ECS.SubsystemID,
AET.UserName--,
-- ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
-- ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
-- EAN.ActionName,
-- ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
-- AET.ResultID,
-- AET.EventID,
-- ECS.ValueTypeID,
-- AET.MissingOld,
-- AET.MissingNew,
-- AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = 'C37D85D6-B725-4E98-A338-B8917CA5784F'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
)
ORDER BY ObjectName
--ORDER BY TimeDetected DESC,
-- UserName DESC
|
|
|
|
|
Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country) or deleted the column(City) or rename the column (Name to FirstName). Now, how I will get the added, deleted or renamed column of the Table.
Please reply ASAP
Thanks.
|
|
|
|
|
You will need to build a schema compare utility.
Make a connection to each of the databases you want to compare and you will have to walk through each one of the tables and compare the column definitions.
Look at sys.columns
http://msdn.microsoft.com/en-us/library/ms176106.aspx[^]
|
|
|
|
|
Then you need to add a ton of artificial intelligence and still guess wrong.
|
|
|
|
|
I disagree with the comment about "artificial intelligence" and "guessing".
The question was How I will get the added, deleted or renamed column of the Table.
My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'.
There is no reason to be sarcastic when replying. Just stay professional.
Now let me get my flak jacket and fireman coat before the responses come flying in.
|
|
|
|
|
And my point is that seeing the differences in the schema won't show you how you got there, what changes were made.
For example, if A,B,C changed to A,D,C -- Was B renamed to D? Was B deleted and D added? There is no way to know with the information given. You may also look at the datatype, but you may also need to investigate the contents of the columns to see whether or not D contains the same contents as B did.
In such a scenario (and based on some data I'm working on now) maybe the original was TRANSACTION_D_T VARCHAR(20) (in yyyy-MM-dd HH:MM:ss format) and it was changed to TRANSACTION_DATE DATETIME -- how would you determine what change was made simply by looking at the schema?
|
|
|
|
|
I agree that seeing a difference in a schema does not tell you how you got there, but the simple matter of detecting a change will initiate an investigation.
No change = No problem (ideally)
Change = Investigation
When there is team development on an application there must be communication among the team to reflect any changes. Using a source control system like Subversion is a good way to see changes made and designating a single person as the responsible party for making any changes to the schema is also a good idea.
|
|
|
|
|
David Mujica wrote: My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'.
Good point; yet there are certain dangers to the approach, which should also be mentioned if one is to make an informed decision.
It's good to see a broad variety of answers, each with their own motivation. You're right that my comment was superfluous after the warning of PIEBALD, and reading back it indeed looks a tad patronizing.
David Mujica wrote: There is no reason to be sarcastic when replying. Just stay professional.
..will try, a little bit harder. Then again, sarcasm is an efficient way to get a point across and to create a few seconds of shock in which one contemplates the previously ignored point.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
I always appreciate the help I get from the folks here.
Sometimes I have to resist jumping on the dog-pile. There are many posts that I just don't respond to because they are just not worth my time.
Thanks again for your (and others) contribution to the community; I've learned a lot here.
David
|
|
|
|
|
You attend project meetings where the DBA tells you.
|
|
|
|
|
I agree with PIEBALD; you do not know why the columns are altered, so best idea is to ignore the changes, and declare "incompatibility with the current version" if they're not as you expected them to be.
If a column is added, it's not always desired to have it visible; if it is visible, it will have consequences for the layout, which means some changes to reports.
If a column is deleted, it's not always desired to ignore it; it could have been renamed during an update, with the intention of renaming it back later - your behaviour would hide a very important bug.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I want to know whether it contains data.If not ,it's easy.
|
|
|
|
|
|
Hello,
I want to store the recent measuring results as well as control samples of older measurements persitently on disk so that the last state can be restored even in the case of a power loss. To make loading the data on startup not unnecessary slow, I want to remove the old, unneeded data from the list.
This means that the measuring results will be continuously appended to the list, but the time when it will be removed is not known in advance.
Would you recommend using a database (ideally some compact system like SQLite so that the user does not have to install a databse server) for this, or are there alternatives?
My idea would be to store the measuring results in one table, using the measuring time as key. In another table, the keys for the last n measuring results would be stored and in a third table the keys of the control samples. If the entries of the first table are not referenced any more from the two other tables, the measuring results should be automatically removed. Is there some concept to acchieve this?
Alex
|
|
|
|
|
This decision should be driven by volume and usage. If you are only processing a few hundred records and do not need to massage the data then an XML or csv file store would be acceptable. Otherwise go for a database.
Why make it complex with multiple tables if you are using text/xml, simply load the entire file into memory and manipulate the collection. Write the collection to the file at regular intervals.
A lot will depend on the criticallity of the data and the frequency of the reads!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you for your answer.
In a previous version, we wrote the complete XML file - however, due to the relative large amount of data (and the limited resources, as it is an embedded system) - this will not be possible in the final release. So I think the database is the most promising.
Which leaves me with the other questions, how to implement this as a weak reference pattern.
Alex
|
|
|
|
|
LionAM wrote: how to implement this as a weak reference pattern.
Databases generally do not directly support the equivalent of weak reference pattern.
One solution is to append the data as needed in a database table and then run a program in background periodically and check for "old" data and purge them from the database. What "old" means and the frequency of purging depends on the volume of your data.
|
|
|
|
|
As a database kind of guy, I strongly recommend a database.
LionAM wrote: To make loading the data on startup not unnecessary slow
I asssume you do this to show a graph or similar of the recent trend, otherwise I don't see why you would load old data. A database makes this easy by allowing you to query only the recent data. You could sort the data descending by date and take only the top n records or you could query the records where the timestamp is greater than the current time minus soome number of minutes or hours. Or you could purge data out of the table after some period (perhaps after summarizing it into another for historical reasons).
Also, as I don't know what sorts of "measuring results" you are dealing with I'll pass along a concept that you may find useful. It's sort of a data compression technique whereby if the measurements don't change much you don't store every individual measurement. For instance, the temperature of a commercial oven for baking bread or cookies needs to be monitored, it can probably vary a little (let's say a tenth of a degree) from the target temperature without causing an alarm, but if it changes a whole degree (for instance) it should be noted. Furthermore, if the temperature stays close to the target temperature for hours on end (as it should) we do want to log some readings (perhaps at least every fifteen minutes). For these reasons, you may want to develop a system that will only store a reading if the difference from the previous stored reading is outside some specified range or if some specified time span has elapsed.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
