|
The following query
<br />
SQL.Format("SELECT DISTINCT s.Description FROM ((((SK_SKILLSINAGES AS sia LEFT JOIN SK_SKILLS AS s ON (sia.SkillCode = s.SkillCode) OR (sia.SkillCode = s.ParentSkillCode AND sia.SubSkillCode = s.SkillCode)) INNER JOIN SK_CATEGORY AS c ON sia.CategoryCode = c.CategoryCode) INNER JOIN SK_CATEGORYGROUPS AS cg ON (sia.GroupCode = cg.GroupCode) AND (cg.GroupCode = c.GroupCode)) INNER JOIN AG_AGESINERAS AS ae ON sia.AgeCode = ae.AgeCode) INNER JOIN AG_ERA AS e ON ae.EraCode = e.EraCode WHERE ( (sia.SubSkillCode Is Null AND s.ParentSkillCode Is Null) OR (sia.SubSkillCode Is Not Null AND s.ParentSkillCode Is Not Null) AND sia.CategoryCode = c.CategoryCode AND sia.GroupCode = cg.GroupCode AND sia.AgeCode = ae.AgeCode) AND ae.Description LIKE 'Middle Ages' AND e.Description LIKE 'Rolemaster' AND s.SkillCode LIKE 'SWORD';"<br />
runs nice and fast however if at the end of the query I add
<br />
OR s.SkillCode LIKE 'LONGSWORD'<br />
The query runs slow. In fact the first query will run instantaneously. the second query takes a few seconds, around 10 seconds. Does anyone know why simply adding an OR statement inflates the time to query a database? Any way of optimizing that somehow?
Hopefully there is enough info here.
Thanks!
Let me add, the first query will return one field while the second query will return 2 fields. Also if you remove
<br />
AND s.SkillCode LIKE 'SWORD'<br />
at the end of the query, the query takes awhile to run again. Again, it seems return 1 record will mean a fast load, but anything larger than that the query slows down considerably. 
|
|
|
|
|
My first question to you would be to ask why you're using LIKE with no wildcards?
I think you'd find slightly better performance with AND ae.Description = 'Middle Ages' AND e.Description = 'Rolemaster' AND s.SkillCode = 'SWORD'
Now that we're doing this, instead of adding your OR statement, try this: AND ae.Description = 'Middle Ages' AND e.Description = 'Rolemaster' AND s.SkillCode IN ('SWORD', 'LONGSWORD')
This may or may not help, mind you. To really get into the nuts and bolts of it I'd need a list of indexes, statistics, and/or an execution plan.
Oh, and what DB platform are you using, anyway?
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
currently Jet Database. Your suggestion with the IN seems to help. I never knew I could do that. Also I never thought about using wildcards for my LIKE statements. However I fail to see the difference between your first suggestion (not the one where I should use IN) and my original suggestion. 
Thanks!
|
|
|
|
|
Like is a pattern matcher. It can never produce better results than =. Your statement, without wildcard, should be optimized to an = but, you are relying upon the query system being smart enough. It is easier for the programmer to be smarter.
SELECT *
FROM Weapons
WHERE Name LIKE '*SWORD*'
This would return all weapons with the letter pattern sword in them.
|
|
|
|
|
Like Michael said. The LIKE keyword is specifically used for pattern matching, and even if you don't use a pattern, the query builder will still probably build the query using the pattern matching algorithm, which is always going to be less efficient than a simple equality algorithm. Depending on the data you're matching, the difference could be inconsequential, or it could be dramatic.
Plus, if you have the field indexed, the equality comparison (or >, <, >=, <=, etc) will use the index much more efficiently. The LIKE operator will do a full index scan, to look for all fields that might match the pattern. The equality operator can use the index as it's intended, by starting with only the rows where that field begins with 'S'. Some database engines may automatically do this with LIKE statements that don't begin with a wildcard. Others may not.
AND, the real reason I brought it up. The IN keyword replaces multiple equality checks. I.e. field IN ('Val1', 'Val2') is translated by the query builder into (field = 'Val1' OR field = 'Val2'). Note that the only difference in this and your original query is the parentheses, which can make all the difference in the world if used properly (or not, as the case may be.)
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
hi,
i got an Access db and i want to make an SQL query
that retrieve a date field and but with this format (mm/yyyy).
how do i do it in SQL (of access db)?
anyone?
|
|
|
|
|
That's one i can answer 
select Format(<datefield>, "mm/yyyy") as <alias> from
replace <datefield>,<alias> and with what u need in your query 
|
|
|
|
|
I'm displaying details on my website, which is taken from my MS access database. Which is simple enough. The details are shown in a table, which you can most probably tell from the extract of code.
The problem is the "Address" appear on the site, but the "Details" do not.
The difference is that on the database, the Address data type is text, and the Details is data type memo.
I believe the problem lie with this difference in data type, and the
If RS("Details") <> "" then.
Hopefully someone can show me the light!
<% If RS("Address") <> "" then
Response.Write("<td height=""20"" valign=""top""><p>")
Response.Write RS("Address") 'This does appear
Response.Write("</p></td>")
End if
%>
<% If RS("Details") <> "" then
Response.Write("<td height=""20"" valign=""top""><p>")
Response.Write RS("Details") 'This does not appear
Response.Write("</p></td>")
End if
%>
---
Peter M
|
|
|
|
|
I've found out you cannot do comparisons with memo fields. You need to put it into a string to compare. Thanks to david wulff.
---
Peter M
|
|
|
|
|
Hi Guys,
I have a three process - database scenario
- The first process inserts data continuously on a 24 hr (TMPDATA). basis, for every 1 second my program insert 6-10 records. It is very fast I'm using a socket connection to get the data.
- 2nd process is to transfer from TMPDATA to ORIGDATA every 5 mins. But Here I do the Locking Isolation level SERIALIZABLE so that the first process will not interfer with this process becuase after the transfer I truncate the TMPDATA table so as to free some space. (24 hrs. continuous)
DTS PROCESS:
begin transaction<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
insert into TMPDATA select * from ORIGDATA<br />
truncate table rop_tmp<br />
SET TRANSACTION ISOLATION LEVEL READ COMMITTED<br />
commit transaction
- 3rd process when I get an Error of "Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction". My program downloads data from ORIGDATA using certain criterias but it failed becuase of this locking. Run only during 12 midnight.
Question: Can you please suggest any isolation level that stops the INSERT for process 1 and allow my process 3 to download data into a file. Please I really need the solution, any idea is welcome. I will try also to look for solution.
/Dabuskol
|
|
|
|
|
Not that this will solve your deadlock but,
I believe you have to set your isolation levels prior to starting the transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin transaction
insert into TMPDATA select * from ORIGDATA
truncate table rop_tmp
commit transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
|
|
|
|
|
Is there a way to detect if a specific DB can support @@IDENTITY ?
Problem is, its possible to change my connection string depending on the database i want to connect to, as a client choice. But not all DB systems make use of @@IDENTITY. My tests here are with MSAccess and MSDE and both support it.
I'm using ADO from C++ in VC 2003.
Thx for any replies, even if negative.
|
|
|
|
|
Although I must admit I don't have first-hand experience with anything but Access or MSDE, you could simply create a test function where you execute an insert and catch any exceptions your SELECT @@IDENTITY throws.
If there's no exception thrown (and you receive a meaningful result) then @@IDENTITY should work later on as well...
Or:
Do not rely on a DB supplying such an information in a certain way but create a stored procedure to perform your inserts and let the procedure return the ID of the newly created element. That way you might be more flexible than hard-coding it in your program.
Regards,
mav
|
|
|
|
|
Assuming you have field like:
intmotyid int Foreign Key
Dttrade date()
Price float
Calculate Sum of price like
Intmotyid |weekly |MTD|Yearly
1 | 10 | 38 | 80
2 | 8 | 19 | 34
3 |32 |72 |73
Group by intmotyid
I need SQL Query.
placement
|
|
|
|
|
Hello
Would be really grateful if someone can help me out. I'm no pro in SQL, but currently I have to build a search function. I'm using ASP and Access 2000 database.
Currently the search i've managed to built can search thru all the tables in the database. Unfortunately the search is not flexible enough.
Here is an example of one of my tables.
--------------------------------------
Product | Model |
------------|-------------------------|
BenQ | FP767 |
Samsung | SyncMaster LCD 192T |
Samsung | SyncMaster LCD 172B |
LG | L1710s |
---------------------------------------
strSearch = Request.form("strsearch")
& "SELECT Product, Model " _
& "FROM LCDMonitor " _
& "WHERE Product LIKE '%" & strSearch & "%' " _
& "OR Model LIKE '%" & strSearch & "%' " _
& "ORDER BY Product;"
Here's the situation. If my search term is samsung then 2 rows will be returned. If I search for syncmaster, 2 rows will also be returned. But if i search for samsung syncmaster, no records are returned. I want it to be able to search for samsung syncmaster as well. And i want it to work even if i search for syncmaster 192T. Currently no records are returned if search for syncmaster 192T.
Do I need to use arrays? If so, how do i do that? Need help with coding.
Quite new to all this stuff, so need all the help I can get. Urgent. If need more information or wat i've posted is not clear, let me know. Thanks
|
|
|
|
|
you should try to parse the search string, I mean split the string to single words, and then try to build different possible sql statement, run all of them, and analyse the result set and pick one or more to the client.
|
|
|
|
|
Hi
Is there any chance you could help me with the coding on how to split the words then build sql statement for each word?
Your help will be very much appreciated.
|
|
|
|
|
Sorry if it's wrong forum but there isn't one for OLEDB... Anyway. Is OLEDB simultaneous-multithread-access safe? Also ... using OLEDB - if different threads need to access the same database is it better to open that database from each thread or is it OK to use the same opened database object from different threads at the same time - reuse the connection?
Sorry for such a basic question. 
*Reposted from ATL forum*
|
|
|
|
|
It depends on your provider. When you use OLE DB, you use some system objects to locate a provider, but after that you're talking directly to the provider object, a component supplied by the vendor.
If they've implemented it properly, if the provider is sensitive to threading issues it should be marked as Apartment model threading. If it isn't, it should be marked as Free or Both.
A connection can usually only process one command at a time, so if you want commands to run concurrently, you'll need to open another connection.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Ok. I need to use Access (assuming microsoft's provider), SQL server (assuming microsoft's provider) and Oracle (assuming oracle's provider). Do you know how good are they? I'm guessing Access is not as good as the two big ones, right? But the big ones should be fine with multithreading, aren't they?
|
|
|
|
|
Since SQL Servre is kinda expensive (5000 euro's per processor), and we don't want to obligate our customers to do that investment, even though SQL Server is, in my opinion, the only way to go.
One of my colleagues came across an Interbase offspring called FireBird, which is open-source and is based on the Interbase code released a long while back by Borland. It looks promising, but it's not there yet as far as I can see. It has a .NET connector already, so switching shouldn't be a big problem...
|
|
|
|
|
If it is mission critical I would be more inclined to go with something more proven.
I use sql Server and Mysql. Mysql is a really good open source database with a lot of documentation and users behind it - worth checking out. Were I to start a business today I would most likely go with Mysql over Sql Server.
B
|
|
|
|
|
Hie all, I have 3 tables - Assessment, Unit, Department.
How can I find :
The numbers of those students who, while attending one or more units run by the department named 'computing', are not attending every unit run by the department named 'computing'.
Hope to hear from you soon.
Thanks.
(ASSESSMENT - Table)
s_no u_no final-grade
S001 Q2 95
S002 Q1 61
S003 Q3 65
S004 Q4 53
S005 Q5 63
S006 Q1 78
S007 Q4 95
S001 Q1 45
S001 Q3 75
S001 Q5 98
S001 Q6 85
(UNIT - Table)
u_no d_no uname
Q1 D1 Programming
Q2 D2 French Language
Q3 D1 Hardware
Q4 D2 French Politics
Q5 D1 Logic
Q6 D1 Operating Systems
Q7 D3 Finance
Q8 D2 French Literature
(DEPARTMENT - Table)
d_no d_name hod(head of dept)
D1 Computing Steve
D2 Language Smith
D3 Business Anne
D4 Accountacy Joe
D5 Art Carol
|
|
|
|
|
The solution lies in Set logic (Math). But I don't see a students table? SO how would I know which student got which grade on which course?
Anyway, If you look at the wanted result set, it's the superset of students who attend AT LEAST one unit that's part of the computing department, minus the students that follow all of em. I.e. students who have a COUNT(attended units in computing) = COUNT(units in computing)
But to show the involved SQL, i'll need the students table and how it's 'connected' to the other tables
|
|
|
|
|
The student's grade is on the ASSESSMENT table, final-grade - column.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
