So I'm working on a vendor's database trying to search it. It's not normalized real well and the tables are kind of elephanted up as it was originally an MS Access database that they ported to MSSQL. I cannot modify the database but I can create views. In any case, what I currently have is a recursive string builder assembling a SQL statement that searches across multiple tables and views that I created to try to streamline things (one of my first SQL projects, it's not pretty). As you can imagine, it's sloooow. Can anyone point me towards a tutorial that would help me optimize this search?
Current query and code:
string strSearch = TextBox1.Text.Split(' ');
string strSelect = "SELECT INVY.ITEMNUM AS Item, " +
"INVY.DESCRIPTION AS Description, STOCK.QTYONHAND AS Quantity, " +
"INVY.UOM AS 'Issue By', STOCK.LOCATION AS Location, INVY.MODEL AS Model, " +
"INVY.UD2 AS IDGroup " +
"FROM INVY LEFT OUTER JOIN STOCK ON INVY.ITEMNUM = STOCK.ITEMNUM " +
"LEFT OUTER JOIN vw_Koogle_ISSREC_Grouping ON INVY.ITEMNUM = vw_Koogle_ISSREC_Grouping.ITEMNUM " +
"LEFT OUTER JOIN INVVEND ON INVY.ITEMNUM = INVVEND.ITEMNUM " +
foreach (string strParam in strSearch)
strSelect = strSelect + "AND ((INVY.DESCRIPTION LIKE '%" + strParam + "%') OR " +
"(INVY.NOTES LIKE '%" + strParam + "%') OR " +
"(INVY.MODEL LIKE '%" + strParam + "%') OR " +
"(INVY.UD2 LIKE '%" + strParam + "%') OR " +
"(INVY.UD1 LIKE '%" + strParam + "%') OR " +
"(INVY.ITEMNUM LIKE '%" + strParam + "%') OR " +
"(vw_RRD_Koogle_ISSREC_Grouping.EQNUM LIKE '%" + strParam + "%') OR " +
"(vw_RRD_Koogle_ISSREC_Grouping.NUMCHARGEDTO LIKE '%" + strParam + "%') OR " +
"(INVVEND.VENDORITEMNUM LIKE '%" + strParam + "%')) ";
strSelect = strSelect.Replace("WHERE AND", "WHERE") +
"GROUP BY INVY.ITEMNUM, INVY.DESCRIPTION, STOCK.QTYONHAND, " +
"INVY.UOM, STOCK.LOCATION, INVY.MODEL, INVY.UD2 " +
"ORDER BY INVY.DESCRIPTION";
sqlMP2Search.SelectCommand = strSelect;
Do you want to get so many tutorial URLs that point to nothing, and might not be able to provide a valid result? I am sure, not.
Please share a sample query with us, that you are running and the result you are getting. It would be really helpful to share the schema with us, and we would be able to help you out with the normalization process, or at least share how you can use other services on SQL Server like stored procedures to minimize the overheads of the queries.
With that update in the question, first thing that anybody is going to say is to remove the concatenation and use parameters in the query—it will protect you from SQL Injection.
Secondly, there are so many LIKE operators, why are you using searches on the table, and trying to match every column with the input. A quick tip would be, use a separate search for each column and then try to aggregate the overall results. This would have a little amount of WHERE clause, and the query would end quickly, yours is having multiple OR clauses, which is not letting SQL Server short-circuit the query either and is making it run on each of the records.
So, you can start by:
1. Add indexers to the columns as needed. You will know where to add them.
2. Create separate stored procedures to find the query results. Then try to aggregate the result of all queries.
3. Use caching to store the results of the most recent queries.
4. Also, try using full text search capabilities of SQL Server.
What else could cause this error beside the sequence not being created?
I have recreated the sequence using SQL code, also recreated it using Oracle SQL Developer "New Sequence" menu but still nothing. I only have a single class and and a single table, being a learning project, I don't want to complicate things. Project made using Maven.
<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN""http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<class name = "com.testing.classes.Person" table = "PERSON">
<id name = "id" type = "int" column = "ID">
<property name = "firstName" column = "FIRST_NAME" type = "string"/>
<property name = "lastName" column = "LAST_NAME" type = "string"/>
<property name = "age" column = "AGE" type = "int"/>
CREATE SEQUENCE CREATE_PERSON_ID INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MINVALUE 1 CACHE 10;
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
Person person = new Person("Name", "Last name", 48);
session.save(person); //Eclipse says the error is here
Well, as you can see in my hibernate.cfg.xml, I'm using SYSTEM, so that isn't the problem unfortunately.
After some more research I found the problem. All the sites with tutorials google suggested were using either <generator class="native"> or <param name="sequence" />SEQUENCE_NAME
The problem is that hibernate devs changed <param name="sequence" /> to <param name="sequence_name" />. It is weird that you get sequence does not exist error, instead of something like invalid value for param or something to suggest that is the problem.
After you install Oracle 11g XE, it will automatically create some tables (from what I've read, some are used for "Application Express", and you shouldn't drop them), but having them listed in Tables view is really annoying.
Is there a way to filter them out? And why do you use $ in table name for?
For some reason, last time I searched on google it didn't showed me anything useful (on any of the first 4 pages), but this time, I got the answer I needed.
1. Use a different user then SYSTEM. I didn't though at this, after all I didn't wanted to complicate things on a learning project.
2. Filter each group using %. When I tried it before it didn't worked, but now after I started the DB again, the filter worked as it should have. Maybe there was an error connecting to the DB, don't know, it's good that now it is working.
When i execute the procedure what i get the output is like -
ALTER PROC [dbo].[PROC_EMPLOYEE_PAY_SLIP]
@MonthName varchar(50), @Year Float
SELECT ISNULL(HR_EmployeeMaster.em_EmplopyeeCode,'') As EmplopyeeCode,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Additions,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN fa_ComponentMaster.Cm_Name ELSE '' END,'') As Deductions,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'A' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As AdditionsAmount,
ISNULL(CASE WHEN fa_ComponentMaster.Cm_ADTag = 'D' THEN Fa_MonthSalary.Ms_Amount ELSE 0 END,0) As DeductionsAmount
LEFT JOIN Fa_MonthSalary ON Fa_MonthSalary.ms_EmpCode = @EmplopyeeCode
LEFT JOIN Fa_MonthDetails ON Fa_MonthDetails.mo_id = Fa_MonthSalary.Ms_MonCode
LEFT JOIN fa_ComponentMaster ON fa_ComponentMaster.cm_code = Fa_MonthSalary.Ms_CompCode
WHERE HR_EmployeeMaster.em_EmplopyeeCode = @EmplopyeeCode AND
(SELECT CONVERT(CHAR(3), Fa_MonthDetails.mo_name)) = @MonthName AND
Fa_MonthDetails.mon_year = @Year