|
In order to help you, you should specify, what is the problem you're experiencing. If it's an error message, post the actual message you get.
The need to optimize rises from a bad design
|
|
|
|
|
better start building that!!!
ask later when stuck, nobody will post you the entire code you know?! 
Helpers will direct you to some article or debug some of your code.
Good luck
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
ok, thank you....
|
|
|
|
|
Hi, I'm working on a site similar to newegg.com and I'm kinda stuck on the filters.
I know the best way would be javascript, however i don't know any library that can accomplish filters like those. So i was doing it in the old way (php > mysql query)
The problem is the optimization, i can't find a good method to do this. i designed the tables this way..
table item {item_id,category_id}
1,1
2,1
3,2
4,1
table filter {item_id,filter_id}
1,100
1,101
1,102
2,99
2,100
3,0
4,5
4,102
Let's say i want to show all items from cateogry 1, so i need to create the filters to be shown. the filters should be something like this
Filter | quantity of items
100 (2)
101 (1)
102 (2)
99 (1)
5 (1)
this is my query
select count(filter_id), filter_id
from filter
where item_id in (select item_id from item where category_id = 1)
and filter_id not in () -- this isn't used in first instance, only when an user click on a filter.
group by filter_id
That's the easy part, now if an user select a filter i need to show the items filtered. This is what i use.
select *
from item
where item_id in (
-- this is only for removing the null results
select item_id
from (
select
-- this shows only those items that have all the filter_id selected by the user
case when count(*) = ".count(explode(',',validateFilters($_GET['filters'])))." then item_id end
from filter
where filter_id in ( ".validateFilters($_GET['filters'])." )
group by item_id
) as p
where item_id is not null
)
and category = 1
and then i have to use that query inside the filter query to get the new filters
select count(filter_id), filter_id
from filter
where item_id in ( all the query above)
and filter_id not in ( ".validateFilters($_GET['filters'])." )
group by filter_id
is there an easier way?
Thank You.
modified on Sunday, October 5, 2008 2:57 PM
|
|
|
|
|
Hi,
I was just thinking, when is it the best to put an index on a foreign key? What I mean, how much records must there be? Lets say I have a customer table with a TitleId field which links to a title table, and the title table has like 10 records, is it still wise to put an index on TitleId in the customer table?
Thanks
Brendan
|
|
|
|
|
Rule of thumb is to create the foreign key index always. Typically the indexes may be quite small but they have a very positive impact on system throughput.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks I will do just that!
Regards
Brendan
|
|
|
|
|
Hi ! I'm a beginner at transact sql and I have a question.
I have a table named person and a table named event.
person has columns personID, personName
event table has columns: eventID, eventName, date
My situation is that I want to know which persons that will go to which event so i have a table called
eventPerson
eventPerson has columns: eventID, personID
How do I construct a question in Transact SQL that returns a table over which persons that goes to wich event
The result would look someting like this
EventNo1 EventNo2 EventNo3
peter 1 0 1
lucy 1 0 1
john 0 1 1
So what I want is a matrix with boolean values. Columns is event. rows is person.
|
|
|
|
|
your query is something like this:
select persons.*,event.*,eventPerson.*<br />
from person,event,eventperson<br />
where person.id = eventperson.id and event.id = eventperson.id
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I think, what you're looking for is PIVOT operator. Have a look at Using PIVOT and UNPIVOT[^]
The need to optimize rises from a bad design
|
|
|
|
|
How can we do:
insert into table (name) values ('nick's')
in sql server.
I tried using :
insert into table (name) values ('nick\'s')
insert into table (name) values ("nick's")
but still could not find the solution. I need to execute the query not from stored procedure. Thanks.
|
|
|
|
|
If you are coding in c# this might help you:
insert into table (name) values (@"nick's")
|
|
|
|
|
do it like this
insert into table (name) values ('Nick''s')
I know it will Help
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
If you are doing this from code you should use named parameters, otherwise you will likely be vulnerable to SQL injection attacks.
|
|
|
|
|
As I've understood, it's not possible to have full-text search indexing on columns larger than 900 bytes. I know I can overcome this problem by adding large columns as non-key included columns to non-clustered indexes. But still I'm not able to use the powerful search capabilities of full-text search. I can only use queries like "select c from t where c like '%find%'". That's, I can not easily look for combination of words. Is there any known solution for searching large fields that can not be easily indexed in SQL Server? As an example, how has it been implemented to search multiple words in codeproject forums? Is it something simple like this?
SELECT * FROM Messages WHERE Title LIKE '%word1%' OR Title LIKE '%word2%'
Thanks in advance for any help.
|
|
|
|
|
Full-text index doesn't have a limit of 900 bytes on indexed columns. The limit is only for key index. So if you have a good primary key, you should not have any problems using full-text indexes.
The need to optimize rises from a bad design
|
|
|
|
|
What do you mean by key index Mika? Primary clustered index? Should I necessarily add a column as a non-key included column to a non-clustered index if I want to have full-text search on it?
Thanks
|
|
|
|
|
Not necessarily clustered index, but primary key or unique key of the table.
See KEY INDEX argument in CREATE FULLTEXT INDEX (Transact-SQL)[^]
The need to optimize rises from a bad design
|
|
|
|
|
Thank you. So there should be no problem. I don't know why somebody told me it's not possible to use it on fields larger than 900 bytes! Maybe I misunderstood the point.
_
modified on Sunday, October 5, 2008 4:47 PM
|
|
|
|
|
i need to know which is the best about from dataset and coding database connection by your self.i need brief description who is better on performance.
|
|
|
|
|
Peformance in database access should be more or less the same, because basically you will have the same queries in both scenarios.
But at client side, a designed dataset will produce lots of souce code, not all really needed. I think a manually created database access will produce far less code than the designed one.
The advantage of the dataset is that is easier to mantain, add and modify the queries and views.
Best regards,
Jaime.
|
|
|
|
|
Hi all,
I am migrating a C++ application to C#. One of the classes can read/write DBF (dBase) files.
The question is:
Does it worth to migrate this class?
What are all the ready-made .net alternatives?
Do these alternatives have some deployment requirements?
Thanks in advance.
Best regards,
Jaime.
|
|
|
|
|
Only thing that comes in mind is an ODBC-driver, which can be used with OdbcConnection Class. I think Microsoft may have free drivers against dBase.
The need to optimize rises from a bad design
|
|
|
|
|
Yeah, I know about the ODBC alternative, but:
* Is it always pre-installed in common client Windows OSs? (XP home/pro, Vista home/pro) ?
* If not, how easy or complex is to configure it when deploying an application that need to use DBF files?
Best regards,
Jaime.
|
|
|
|
|
Don't know if it's pre-installed. I would believe that not. I think that MDAC installs also dBase drivers, but I'm not sure.
The installation of a single driver is basically copying the dll files and then registering the dll's. After that a DSN can be created programmatically or using Control Panel.
The need to optimize rises from a bad design
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
