|
Try the following:
SELECT *
FROM SK_SKILLSINAGES AS sia
INNER JOIN AG_AGESINERAS AS ae
ON sia.AgeCode = ae.AgeCode
AND ae.Description = 'Middle Ages'
INNER JOIN SK_CATEGORYGROUPS AS cg
ON sia.GroupCode = cg.GroupCode
INNER JOIN SK_CATEGORY AS c
ON sia.CategoryCode = c.CategoryCode
INNER JOIN AG_ERA AS e
ON ae.EraCode = e.EraCode
AND e.Description = 'Rolemaster'
INNER JOIN PR_DEVELOPMENTPOINTS AS pd
ON sia.CategoryCode = pd.CategoryCode
AND sia.GroupCode = pd.GroupCode
AND pd.ProfessionCode = 'ANIMIST'
LEFT JOIN SK_SKILLS AS s
ON (sia.SkillCode = s.SkillCode)
OR (sia.SkillCode = s.ParentSkillCode AND sia.SubSkillCode = s.SkillCode))
WHERE ( (sia.SubSkillCode Is Null AND s.ParentSkillCode Is Null)
OR (sia.SubSkillCode Is Not Null AND s.ParentSkillCode Is Not Null)
ORDER BY sia.AgeCode, sia.GroupCode, sia.CategoryCode, sia.SkillCode, sia.SubSkillCode;
I have tried to simplify and reorder the SQL. You have a number of joins that restrict the number of records returned (e.g. your "pd.ProfessionCode = 'ANIMIST'" clause). You might want to try moving the more restrictive tables to the top of the "from" clause.
My guess is that the "or" statements involved with the link to SK_SKILLS are the big performance drain (because many databases won't bother to use the indexes) You can test this by removing that part of the statement). A possible way of getting around the "OR" problem is to use a union-join:
SELECT * FROM tables
WHERE sia.SkillCode = s.SkillCode
UNION ALL
SELECT * FROM tables
WHERE sia.SkillCode = s.ParentSkillCode
AND sia.SubSkillCode = s.SkillCode
ORDER BY <columns>
This approach allows the database engine to use separate indexes for the two different links to the skills table.
Hope this helps.
Andy Harman
|
|
|
|
|
You're right about the OR in the SQL. It seems if I remove it, the query goes through faster. Using the UNION query does solve the problem too. Thanks for the reply!
|
|
|
|
|
I have a slight problem inserting the proper result into an MS-SQL table. The result should be <14> however all I get is PRE. BTW, the SQL command not broken appart like you see it on this limited width posting..........
I thought using single quotes desiganted a 'string'? Help........
string pattern1 = @"(<\d{2,3}>)";
Regex r1 = new Regex(pattern1, RegexOptions.Compiled);
Match PRE = r1.Match(stringOfData);
{
try
{
connection1.Open();
SqlDataAdapter1.SelectCommand.CommandText = "INSERT INTO
tblMyUdpServer (col_PRE) VALUES ('PRE')";
SqlDataAdapter1.SelectCommand.ExecuteNonQuery();
connection1.Close();
}
catch(SqlException error)
{
MessageBox.Show(error.Message.ToString());
}
listBox1.Items.Add(PRE.ToString());
}
}
|
|
|
|
|
Ummmm... Are you trying to say that the VALUES('PRE') in your CommandText is supposed to be the result of Match PRE = r1.Match(stringOfData) ???
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Yes, this particular Regex matches numbers between < >. For instance <14>, however only PRE is written to the database........
|
|
|
|
|
You are inserting hardCoded "PRE" value rather than variable PRE
Change your sql to
SqlDataAdapter1.SelectCommand.CommandText = "INSERT INTO
tblMyUdpServer (col_PRE) VALUES (" + PRE.Value + ")";
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
Hello All,
I have a problem that I cannot seem to solve. My guess is that I am thinking wrong. The problem is probably really simple but I cannot seem to solve it. I have two tables, one called test_user and one called test_answer. (Below all types in the tables are integers)
test_user looks like this:
user_id,time_answered,+some other statistics
1 0
2 0
3 0
test_answer looks like this:
test_id,user_id,answered
0 1 1
0 2 1
0 3 0
1 1 1
1 2 1
1 3 0
3 1 1
3 2 0
3 3 0
Basicly test_answered contains a 1 in the answered if the user has answered a question on test test_id.
What I want to do is sum up all the answers and update the test_user table. If I do this SQL-query then I get the table I want to update test_user with.
select user_id,sum(answered) as sum from test_answer where answered = 1 group by user_id
(The output will be:
user_id,sum
1 3
2 2 )
Is there a simple way to update the test_user tables without doing a select into (and destroying all other data I have in the table.)
As I told you this is probably an super simple question but I cannot get it right?
hope you guys can help me!
|
|
|
|
|
What do you mean it is destroying all the other data in the table? What is your existing UPDATE query?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
What I mean is that if I do the select with an select into statement it will create a new table for me with the result. I dont have an update statement, because all the ones I tried generates errors 
|
|
|
|
|
UPDATE test_user
SET no_answered = SUM(answered)
FROM test_answer
WHERE user_id = test_answer.user_id I haven't tested this, but it's along these lines.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I'm not particularly sure that this is what you want.
You want to update or insert into the test_user table depending on the records in test_answer, right?
If that is the case, this might be the answer:
INSERT INTO test_user (user_id, time_answered)
(
SELECT user_id, SUM(answered)
FROM test_answer
WHERE answered = 1 AND user_id NOT IN (SELECT user_id FROM test_user)
GROUP BY user_id
);
UPDATE test_user
SET time_answered = SUM(answered)
FROM test_answer
WHERE test_answer.user_id = test_user.user_id;
|
|
|
|
|
THE SHORT VERSION OF THE QUESTION
How would I leverage SQL Server 2005 Notification Services to notify my asp.net page (or control) that the data it has cached is out of date? The Notification Samples get into specific data (stock prices) being updated past a target value which is a little too detailed for what I want. I just want to be notified when a table change has occured and should expire my cache that is dependant upon that data.
THE LONG VERSION OF THE QUESTION
So I have been reading up on new ways to cache with ASP.NET 2.0 and came across a good article regarding it here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/SecFeatNT2.asp
It has a note in the article that states:
Note Microsoft SQL Server 2005 supports a completely different method of SQL Cache Invalidation. You can configure SQL Server 2005 to notify your ASP.NET application whenever changes have been made to a database, a database table, or a database row. This means that the ASP.NET Framework does not need to constantly poll a SQL Server 2005 database for changes.
After looking around for quite a while I discovered Notification Services within SQL Server 2005 which is rather cool in that instead of the ASP.NET application polling the server every so often it actually subscribes to the Notification Service and is notified if the event has occurred.
There are some Notification Services samples that one can install and play around with and I found the framework to be pretty flexible and like it a lot. However, I am still a little confused on how to accomplish the approach in the ASP.NET caching article using Notification Services.
Any direction or help would be appreciated.
-james
___________________________
J A M E S C O L E M A N
Practice Director, Microsoft
AGENCY.COM
jcoleman@agency.com
http://www.agency.com
|
|
|
|
|
Hi Everybody
Is it better to apply a single complex query than multiple queries.
Has Any body any idea
|
|
|
|
|
Perhaps if you broke your complex question into multiple understandable questions, someone will provide an answer. 
Chris Meech
I am Canadian. [heard in a local bar]
I think people should be required to have an operator's permit to use the internet. John Simmons
I have a feeling that if the millions of man hours wasted every year by geeks trying to get various video and sound cards working under Linux were put into some useful endeavor we'd have solved world hunger, we'd have peace and aids would be no more. [JOS poster]
|
|
|
|
|
I wanna fetch some data from a database. The data I want is lying in different tables.
My Question is
Is it better to use a single query containing joins than using different queries to fetch that data
|
|
|
|
|
Use the joins. Joins are not in themselves complext, however the data model can be.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
It is better to drink water than coffee. 
If you mean, "is it going to be faster to run a single query", then the answer is "yes, almost always".
If you mean, "is it going to be easier to run multiple queries", then the answer to that is also "yes, almost always".
If you mean, "which of the options is going to solve world hunger and remove GW Bush from office", then I would have to say "neither, unless you're progamming the bush-cheney 2004 web-site".
my blog
|
|
|
|
|
I want to write a function what use ado.net update database. in the function,
it can check record automatically then insert or update database.Have some better method?
|
|
|
|
|
Create a stored procedure that does the check and if acceptable insert or update the record. Call the stored procedure from your .NET application.
If necessary you can return success or failure values back to your .NET application if you need to know if the insert/update was successful or if the check denied the update and so on.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Hi friends...
i installed Microsoft Data Engine (MSDE) 1.0 in my machine...but this will not start the service automatically, so i searched in microsoft site and according to them..
Scm.exe Utility Examples
The following are examples that use the Scm.exe utility to start, stop, and modify the MSSQLServer Service:
Start the MSSQLServer Service
scm -Action 1 -Service mssqlserver
i tried this many times with that SCM.exe, but what the hell that again popups the syntax screen..!!!what is wrong with ?? their documentation or scm.exe.?????
please help me..!!!!
i would like to name the next generation of c++ as c++++ 
|
|
|
|
|
renjith_sree wrote:
i would like to name the next generation of c++ as c++++
I think you are too late - Microsoft already named it C# (if you notice the # symbol can be made up of 4 + symbols)
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Colin Angus Mackay wrote:
Microsoft already named it C# (if you notice the # symbol can be made up of 4 + symbols)
are you joking..?? c# is the next generation of c ++ ??
i never saw such an ugly syntaxed language...
if they do so they killed the beautiness of c++
that looks like a silly script..!!!!
yuck..!!!
if you really eager to know about the next gen of c, search for the laguage named 'D'. thats really nice ..!!!
would like to name the next generation of c++ as c++++
|
|
|
|
|
renjith_sree wrote:
are you joking..??
About C# being the next generation of C++? Only partly. Microsoft did use the sharp (it is supposed to be the musical sharp Unicode 266F rather than the hash character) because it can be split into 4 plus symbols. And Microsoft are selling it on the idea that it is a step forward from C++
renjith_sree wrote:
i never saw such an ugly syntaxed language...
Yeah! I know all those curly braces are really horrible. 
renjith_sree wrote:
if they do so they killed the beautiness of c++
Have you been taking Enlish lessons from an American? Lets take a noun "beauty" and modify it so it is.... A noun! Ergo: Beautiness. It like color becomes colorize (for a verb) and colorization (for a noun) despite the fact that color is both a verb and a noun.
As for C++, what IS beautiful about C++?
renjith_sree wrote:
that looks like a silly script..!!!!
Not as silly as VB.NET!
renjith_sree wrote:
if you really eager to know about the next gen of c, search for the laguage named 'D'.
I've heard of 'D' already. However, it is unlikely to put food on my table or a roof over my head so I'll stick with C# thanks.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Hi, writing what should be simple enough programme to access MS Access table in C. Using Borland (unfortunate constraints dictate this). I have downloaded all the neccesary header files and SDKs available for ODBC. Programme is written and compiles without errors or warnings however when I ask to make or build project. All my SQL functions are declared unresolved by the linker. I think I might be missing a file called bodbc32.dll, a link library for Borland when using ODBC.
Below is an example of the error message.
[Linker Error] Unresolved external 'SQLBindParameter' referenced from C:\DOCUMENTS AND SETTINGS\LAWRED2\MY DOCUMENTS\WORK FOLDER\PROGRAMMES\SERIAL\SERIALREADER.OBJ
If someone could explain this. Maybe it is the fact I am missing the link library. Anyone know of it, have experience of this sort of lark, anyone have that file. Google doesnt give one page about bodbc32.lib.
Help!!
|
|
|
|
|
You are missing a required .lib file for linking. I think if you check the file SERIALREADER.c (or what ever source type it might be), it is likely #including a header file like bodbc32.h or something like that. If you can then find the location of that header file, there should be a directory above that include directory,like 'LIB' that would normally contain the .lib file to link with.
That's my best guess to start with.
Chris Meech
I am Canadian. [heard in a local bar]
I think people should be required to have an operator's permit to use the internet. John Simmons
I have a feeling that if the millions of man hours wasted every year by geeks trying to get various video and sound cards working under Linux were put into some useful endeavor we'd have solved world hunger, we'd have peace and aids would be no more. [JOS poster]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
